query to find closest lesser date
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 &lt;= 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 &lt;= a.ActionDate
      GROUP BY a.ActionDate
    ) AS grp
      ON  grp.ActionDate = a.ActionDate
 JOIN
    Rates AS r
      ON  r.StartDate = grp.StartDate ;