RE: Need ideas for SQL join select to replace logical view

[rob@xxxxxxxxx]

Let me add this.  There is a canned package that makes heavy usage of 
"effective dates".  For example a file called EMPLRAT.  Which may have 
records like
Employee#    PayRate   DateEffective
09123        400.00      2006-02-15
09123        450.00      2007-02-15
09123        500.00      2008-02-15

So, yes, you can have dates effective out in the future.  Therefore 
checking the "end date" for null or some such thing would not be an 
indicator of which is in effect.

Not that it happens much, but let's say you did add a field like end date. 
 And in the above sample you inserted a row in between the last two, 
2007-10-15.  You'd have to figure out which records had an end date 
exceeding that, with a start date preceding that, and adjust them 
accordingly.  Then you'd have to figure out if there were any records with 
a start date preceding the new row, and if so, use the first start date 
preceding that and store that into the new row and it's end date.  No 
thanks.  One date field will be sufficient.  Sort of like

Select  PayRate from emplrat
where employee#=09123 and DateEffective<Current date
order by DateEffective Desc
fetch first row only

or

with t1 as (
  select max(employee#) as employee#, max(DateEffective) as DE
  from emplrat
  where employee#=09123 and DateEffective < current date)
select PayRate from t1, emplrat
where t1.employee#=09123
  and T1.DE = emplrat.DateEffective


Both tested and effective.

Rob Berendt