>Well, you DID say the last one was too easy
See if this is close to what we want.
;with cte_labor2 as
(select TimeSheets.*, [HourlyRate] = (select top 1 HourlyRate from WorkerRates where WorkerRates.EmployeeID =
TimeSheets.EmployeeID and TimeSheets.WorkDate >= (CASE WHEN WorkerRates.RateEffectiveDate > WorkerRates.RateAddedDate THEN WorkerRates.RateAddedDate ELSE WorkerRates.RateEffectiveDate END)
ORDER BY CASE WHEN WorkerRates.RateEffectiveDate > WorkerRates.RateAddedDate THEN WorkerRates.RateAddedDate ELSE WorkerRates.RateEffectiveDate END DESC) from TimeSheets)
select EmployeeID, MONTH(WorkDate) as mWork, YEAR(WorkDate) as YDate, SUM(HourlyRate*HoursWorked) as LaborAmount from cte_labor2 group by EmployeeID, YEAR(WorkDate), MONTH(WorkDate)
If it's not broken, fix it until it is.
My Blog 
