×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
As an Amazon Associate we earn from qualifying purchases.
This thread ...
RE: Need ideas for SQL join select to replace logical view, (continued)
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.