Re: SQL With Effective Dates

Thanks All! I can now move on to the rest of this application! Rudy Fernandes (rferdy@americasm01.nt.com) wrote: : 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 rwilson@ntsource.com