Need query to select max effective date based on date range

[M Skabialka]

I have a database which tracks employee hours on contracts. The rate
charged for labor depends on the labor category, and the date the work was
performed.

These rates change once or twice a year, and I want to make sure that I
choose the rate applicable for the day they work.

Here's examples of the tables:

tblRate
LaborCategory EffectiveDate Rate
AA 1/1/03 $10.00
AA 6/10/03 $10.50
AA 1/1/04 $11.00
BB 1/1/03 $17.00
BB 6/10/03 $18.50
BB 1/1/04 $21.00
etc

tblHoursEntry
EmpName LaborCategory Hours DayWorked
SMITH AA 8.0 1/10/03
SMITH AA 8.0 6/14/03
SMITH AA 7.5 12/31/03
SMITH AA 7.0 1/14/04
JONES BB 9.0 1/10/03
JONES BB 10.0 6/14/03
JONES BB 6.0 12/31/03
JONES BB 6.0 1/14/04

How can I write a query that returns the correct rate for the day worked:
EmpName LaborCategory Hours Rate Total
SMITH AA 8.0 $10.00 $80.00
SMITH AA 8.0 $10.50 $84.00
SMITH AA 7.5 $10.50 $78.75
SMITH AA 7.0 $11.00 $77.00
JONES BB 9.0 $17.00 $153.00
JONES BB 10.0 $18.50 $185.00
JONES BB 6.0 $18.50 $111.00
JONES BB 6.0 $21.00 $121.00

I have tried all sorts of grouping queries using Min and Max on the
EffectiveDate (occasionally the rate goes down so I can't use Max on the
rate) but I always get too many results, showing rates for many effective
dates, so showing each employee two or three times for each labor day worked
instead of once. I even tried queries that use other queries but still have
no luck.

Thanks for any help,
Mich

 

[Michel Walsh]

Hi,


Untested, but the following should do:

----------------------------
SELECT h.EmpName,
LAST(h.LaborCategory) As category,
LAST(h.Hours) As workedHours,
h.DayWorked,
LAST(r.Rate) As ApplicableRate

FROM ( HoursEntry As h INNER JOIN Rate as r
ON ( h.LaborCategory = r.LaborCategory )
AND ( h.DayWorked >= r.EffectiveDate)
) INNER JOIN Rate As s
ON ( h.LaborCategory = s.LaborCategory )
AND ( h.DayWorked >= s.EffectiveDate)

GROUP BY h.EmpName, h.DayWorked, r.EffectiveDate

HAVING r.EffectiveDate=MIN(s.EffectiveDate)