;with cte_labor2 as (select TimeSheets.*, [HourlyRate] = (select top 1 HourlyRate from WorkerRates where WorkerRates.EmployeeID = TimeSheets.EmployeeID and TimeSheets.WorkDate >=WorkerRates.RateEffectiveDate and TimeSheets.WorkDate >=WorkerRates.RateAddedDate ORDER BY WorkerRates.RateEffectiveDate DESC) from TimeSheets) select EmployeeID, MONTH(WorkDate) as mWork, YEAR(WorkDate) as YDate, SUM(HoursWorked) as HoursWorked, SUM(HourlyRate*HoursWorked) as LaborAmount from cte_labor2 group by EmployeeID, YEAR(WorkDate), MONTH(WorkDate)