Re: SQL With Effective Dates

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) > >-- >Rob Wilson >rwilson@ntsource.com > If you're doing this strictly in SQL I'd select * from route where (end_date >= today and eff_date <= today) or eff_date is null into temp whatever; Then it's a real fast select on the one or two records (provided there's only one record with nulls and no overlaps) in the temp table based on max(eff_date). I know it's two select statements, but the second one shouldn't add too much overhead. In 4gl use the single select and add order by eff_date desc so that the non null value explicitly sorts first (it should without the desc but why tempt fate) and use the first row you encounter. carlos Currently on hiatus from unemployment. ---------------------- Do you do Linux? :) Get your FREE @linuxstart.com email address at: http://www.linuxstart.com