查詢以查找最近的較小日期

我有一張價格表。每個費率都有一個有效的開始日期。（任何費率的結束日期都暗示存在具有較新生效開始日期的記錄。）我還有一張包含活動的表格。每個活動都在一個日期發生。

將活動與該日期有效的費率相匹配的最佳方式是什麼？

（不幸的是，我沒有能力修改資料結構，因此為費率添加明確的結束日期已經過時了。）

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

或GROUP BY解決方案：

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 ;

引用自：https://dba.stackexchange.com/questions/27823