SQL With Effective Dates
Rob Wilson needed one SQL to pick the currently effective temporary route row or else the master row (null dates). Art S. Kagel proposed an OUTER join of master rows to rows effective today. Doug Agnew gave a UNION with NOT EXISTS, and Rudy Fernandes a MAX(NVL(eff_date)) subquery. Jonathan Leffler recommended Snodgrass's book on time-oriented SQL. No confirmation.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have a table with these columns:
create table route (
route_id serial,
orig_id char(5), --Origination id
dest_id char(5), -- Destination id
eff_date date,
end_date date
);
It actually has more, but these are the important ones. The problem I am
running into has to do with the eff_date and end_date columns. This table
is meant to allow for temporary changes to the "master" shipping
schedule. If it is a master entry then the eff_date and end_date will
be null. If it is a temporary change, the eff_date and end_date will
both have a date in them. The problem that I am running into is that I
want to select out a row that is currently in effect or the master row
if there is no temporary change in effect. I would love to be able to
do this in one SQL. (It is ok to assume that effective periods will not
overlap)
--
Rob Wilson
rwilson@ntsource.com
Rob Wilson wrote:
>
> I have a table with these columns:
>
> create table route (
> route_id serial,
> orig_id char(5), --Origination id
> dest_id char(5), -- Destination id
> eff_date date,
> end_date date
> );>
> It actually has more, but these are the important ones. The problem I am
> running into has to do with the eff_date and end_date columns. This table
> is meant to allow for temporary changes to the "master" shipping
> schedule. If it is a master entry then the eff_date and end_date will
> be null. If it is a temporary change, the eff_date and end_date will
> both have a date in them. The problem that I am running into is that I
> want to select out a row that is currently in effect or the master row
> if there is no temporary change in effect. I would love to be able to
> do this in one SQL. (It is ok to assume that effective periods will not
> overlap)
SELECT d.*, t.*
FROM route d, OUTER t
WHERE d.orig_id = <val> AND d.dest_id = <val>
AND d.orig_id = t.orig_id AND d.dest_id = t.dest_id
AND d.eff_date IS NULL
AND t.eff_date <= TODAY AND t.end_date >= TODAY
...
Now you have a single row for each route with either a NULL t.route_id
indicating that there is ONLY a default route or a non-null t.route_id
indicating that there is a temporary override routing. That's about the
best you can do.
Art S. Kagel
I'm assuming that a temporary entry matches the master on 'orig_id, dest_id'
and that you are interested in the route_id associated with the 'currently
effective' rows. I also assume that the eff_date and end_date are inclusive
limits:
Then the following should work:
Select route_id, orig_id, dest_id from route a
where eff_date is null and not exists (select * from route b wherea.orig_id=b.orig_id and a.dest_id=b.dest_id
and b.eff_date is not null and b.eff_date <= current and
b.end_date >= current) {selecting master records that are not replaced}
UNION
select route_id, orig_id, dest_id from route a
where eff_date is not null and eff_date <=current and end_date >= current;{selecting all current temporary records}
Syntax may be a little off, but I think you get the idea.
"Rob Wilson" <rwilson@ntsource.com> wrote in message
news:JCbX4.898$Vj5.3515@newsfeed.slurp.net...
> I have a table with these columns:
>
> create table route (
> route_id serial,> orig_id char(5), --Origination id
> dest_id char(5), -- Destination id
> eff_date date,
> end_date date
> );
>
> It actually has more, but these are the important ones. The problem I am
> running into has to do with the eff_date and end_date columns. This table
> is meant to allow for temporary changes to the "master" shipping
> schedule. If it is a master entry then the eff_date and end_date will
> be null. If it is a temporary change, the eff_date and end_date will
> both have a date in them. The problem that I am running into is that I
> want to select out a row that is currently in effect or the master row
> if there is no temporary change in effect. I would love to be able to
> do this in one SQL. (It is ok to assume that effective periods will not
> overlap)
>
> --
> Rob Wilson
> rwilson@ntsource.com
>
This should give it to you :
SELECT a.<whatever> FROM route a
WHERE a.orig_id = <var>
AND a.dest_id = <var>
AND NVL(eff_date, MDY(1,1,1900)) = (
SELECT MAX(NVL(eff_date, MDY(1,1,1900)))
FROM route b
WHERE a.orig_id = b.orig_id
AND a.dest_id = b.dest_id
AND (TODAY BETWEEN b.eff_date AND b.end_date
OR eff_date IS NULL));
Rudy
Rob Wilson wrote:
> I have a table with these columns:
> create table route (
> route_id serial,
> orig_id char(5), --Origination id
> dest_id char(5), -- Destination id
> eff_date date,
> end_date date
> );> It actually has more, but these are the important ones. The problem I am
> running into has to do with the eff_date and end_date columns. This table
> is meant to allow for temporary changes to the "master" shipping
> schedule. If it is a master entry then the eff_date and end_date will
> be null. If it is a temporary change, the eff_date and end_date will
> both have a date in them. The problem that I am running into is that I
> want to select out a row that is currently in effect or the master row
> if there is no temporary change in effect. I would love to be able to
> do this in one SQL. (It is ok to assume that effective periods will not
> overlap)
>
Rob Wilson wrote:
> I have a table with these columns:
>
> create table route (
> route_id serial,
> orig_id char(5), --Origination id
> dest_id char(5), -- Destination id
> eff_date date,
> end_date date
> );>
> It actually has more, but these are the important ones. The problem I am
> running into has to do with the eff_date and end_date columns. This table
> is meant to allow for temporary changes to the "master" shipping
> schedule. If it is a master entry then the eff_date and end_date will
> be null. If it is a temporary change, the eff_date and end_date will
> both have a date in them. The problem that I am running into is that I
> want to select out a row that is currently in effect or the master row
> if there is no temporary change in effect. I would love to be able to
> do this in one SQL. (It is ok to assume that effective periods will not
> overlap)
You've been given the answers that'll get you going.
If you want the complete answer, you need the book.
Developing Time-Oriented Database Applications in SQL
Richard T Snodgrass, 2000, Morgan Kaufman, ISBN 1-55860-436-7.
http://www.mkp.com
It covers more than you ever wanted to know about such queries and updates.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN
#include <disclaimer.h>