Matching labour costs to a timesheet - rate per person per day

I'm having difficulty trying to find the correct rate to use for each employee on a given date.

For each Date, Employee ID and Rate Type, Find the correct Rate Amount. I then need to multiply those two efficiently. The data shown is similar in format to what I'm trying to process in my role.

Timesheet Format

Date	        EmpID	Rate    HoursWorked	RateAmount	LabourCost
3/01/2020	JBR001	BR1	8		
10/01/2020	JBR001	BR1	8		
10/01/2020	JBR001	OT1	4		
17/01/2020	JBR001	BR1	8		
24/01/2020	JBR001	BR1	8		
31/01/2020	JBR001	BR1	8		
7/02/2020	JBR001	BR1	8		
14/02/2020	JBR001	BR1	8		
13/03/2020	JBR001	BR1	8		
20/03/2020	JBR001	BR1	8		
20/03/2020	JBR001	OT1	4		
27/03/2020	JBR001	BR1	8		
3/04/2020	JBR001	BR1	8		
17/04/2020	JBR001	BR1	8		
4/01/2020	BJL001	BR1	8		
10/01/2020	BJL001	BR1	8		
27/02/2020	BJL001	BR1	8		
27/02/2020	BJL001	OT1	3		
4/03/2020	BJL001	BR1	8		
10/03/2020	BJL001	BR1	8		
10/03/2020	BJL001	OT1	4		
28/03/2020	BJL001	BR1	8		
3/04/2020	BJL001	BR1	8		

Labour Rate Table

EmpID	Rate	RateStart	RateAmount
JBR001	BR1	1/01/2020	10
JBR001	BR1	1/03/2020	11
JBR001	OT1	1/01/2020	15
JBR001	OT1	1/04/2020	16
BJL001	BR1	1/01/2020	12
BJL001	BR1	1/03/2020	13
BJL001	OT1	1/01/2020	15
BJL001	OT1	1/04/2020	16

If somebody could advise me how to go about mashing this information together I would be very grateful.
Thank you!

I have been successful in combining this information in PowerQuery/M, but the query was very very slow. I'm hoping somebody could share the power of T-SQL with me!

One way of getting the effective RateAmount for a Timesheet entry is a correlated TOP 1 query (Fiddle)

SELECT TS.*,
       LR.RateAmount
FROM   Timesheet TS
       CROSS APPLY (SELECT TOP 1 *
                    FROM   LabourRate LR
                    WHERE  TS.EmpId = LR.EmpId
                           AND TS.Rate = LR.Rate
                           AND LR.RateStart <= TS.Date
                    ORDER  BY LR.RateStart DESC) LR 

The above will need an index on

LabourRate(EmpId,Rate,RateStart) INCLUDE ( RateAmount) 

If the tables aren't too large the above will likely be adequate - though there are other potentially more efficient ways.

If you are on SQL Server 2022 the IGNORE NULLS option to LAST_VALUE could also potentially be useful here to keep track of the effective rate without needing a correlated query (Fiddle 2)

WITH T1 AS
(
SELECT 1 AS Code, [Date], EmpId, Rate, HoursWorked, NULL AS RateAmount
FROM   Timesheet
UNION ALL
SELECT 0 AS Code, [RateStart],[EmpId], [Rate], NULL, RateAmount
FROM LabourRate
), T2 AS
(
SELECT *, 
       EffectiveRateAmount = LAST_VALUE(RateAmount) IGNORE NULLS OVER (PARTITION BY EmpId, Rate ORDER BY [Date], Code)
FROM T1
)
SELECT [Date], EmpId, Rate, HoursWorked, EffectiveRateAmount
FROM T2
WHERE Code = 1

You can use a CROSS APPLY(SELECT TOP 1 ...) pattern to select the appropriate rate amount for each timesheet record. A CROSS APPLY is like a join to a subquery, where that subquery can reference tables earlier in the FROM clause.

In your case, it appears that you need to match on EmpID and Rate, and then select the latest Labour Rate Table row with a RateStart value equal or less than the timesheet date.

This can be done as follows:

SELECT T.*, R.RateAmount, T.HoursWorked * R.RateAmount AS LabourCost
FROM TimeSheet T
OUTER APPLY (
    -- Most recent rate as of timesheet date
    SELECT TOP 1 *
    FROM LabourRateTable R
    WHERE R.EmpID = T.EmpID
    AND R.Rate = T.Rate
    AND R.RateStart <= T.Date
    ORDER BY R.RateStart DESC
) R
ORDER BY T.EmpID, T.Date

Note that I used an OUTER APPLY instead of a CROSS APPLY. An outer apply is like a left join variant of a cross apply. If no match is found, it will still return a result having null RateAmount value (and null calculated LabourCost). This should not occur if your data is good, but returning null values may be preferred over quietly discarding unmatched data.

See this db<>fiddle for a demo.

Sample results: