query to find closest lesser date

Question

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

Wouldn't the correlated subquery be executed many times? Can you guys think of any other query that would perform better on really big datasets? Rather than using joins with corralated subquery, maybe by leveraging LAG/LEAD functions? I'm just wondering.. would appreciate any input or idea to solve the performance issue I'm facing with similar problem.. — Tom, Sep 18 '20 at 16:24

ypercubeᵀᴹ · Accepted Answer · 2012-10-29T17:39:30.020

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 ;

query to find closest lesser date

1 Answers1