# query to find closest lesser date

I have a table with rates. each rate has an effective starting date. (the ending date of any rate is implied by the existence of a record with a newer effective starting date.) I also have a table with activities. Each activity occurs on a date.

What is the best way to match an activity to the rate that was effective on that date?

(unfortunately I don't have the ability to modify the data structure, so adding an explicit ending date for a rate is out.)

``````rates:
StartDate Rate
9/1/2010    17.00
10/1/2010   18.70
11/1/2010   20.00

Activities:
WorkCenter  ActionDate Hours
WC1         9/30/2010   10
WC1         10/1/2010   5
WC2         10/30/2010  8
WC2         11/3/2010   9

Desired result:
Workcenter ActionDate   Hours   Rate    Cost(=rate*hours)
WC1         9/30/2010   10      17.00   170.00
WC1         10/1/2010   5       18.70    93.50
WC2         10/30/2010  8       18.70   149.60
WC2         11/3/2010   9       20.00   180.00
``````
-

``````SELECT
a.WorkCenter
, a.ActionDate
, a.Hours
, r.Rate
, r.Rate * a.Hours  AS Cost
FROM
Activities AS a
JOIN
Rates AS r
ON  r.StartDate =
( SELECT MAX(StartDate)
FROM Rates
WHERE StartDate <= a.ActionDate
) ;
``````

or a `GROUP BY` solution:

``````SELECT
a.WorkCenter
, a.ActionDate
, a.Hours
, r.Rate
, r.Rate * a.Hours  AS Cost
FROM
Activities AS a
JOIN
( SELECT
a.ActionDate
, MAX(r.StartDate) AS StartDate
FROM
Activities AS a
JOIN
Rates AS r
ON r.StartDate <= a.ActionDate
GROUP BY a.ActionDate
) AS grp
ON  grp.ActionDate = a.ActionDate
JOIN
Rates AS r
ON  r.StartDate = grp.StartDate ;
``````
-