>Here's the one I've used...
>
>

>SELECT TimeSheets.EmployeeID, Month(WorkDate) as MonthPay , YEAR(WorkDate) AS YearPay,
>   SUM(TimeSheets.HoursWorked * WorkerRates.HourlyRate)  AS LaborDollars
>        FROM TimeSheets
>              JOIN WorkerRates ON WorkerRates.EmployeeID = TimeSheets.EmployeeID 
>
>                  WHERE WorkerRates.RateEffectiveDate = 
>                        (SELECT MAX(RateEffectiveDate) FROM WorkerRates RatesTemp
>                                      WHERE RateEffectiveDate <= WorkDate AND 
>                                                     RateAddedDate <= WorkDate AND 
>                                                   RatesTemp.EmployeeID = TimeSheets.EmployeeID)
>    GROUP BY TimeSheets.EmployeeID, Month(WorkDate),YEAR(WorkDate)
>

>
>Obviously, you could use a CTE as well.
>
>Our execution plans are different....yours is interesting....obvoiusly, more than one way to do it.
>
>Later tonight I'll try to post the execution plans.
>
>Thanks for participating...

I see, this seems now like a more obvious and direct approach, I wonder why I didn't think that straight? :)

Thanks.