SQL With Effective Dates
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>