9

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
1
  • 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
    Commented Sep 18, 2020 at 16:24

1 Answer 1

12
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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.