Stuart1044 Stuart1044 - 8 months ago 26
SQL Question

Looking Up Value to Return Hourly Rate

I have a table storing hourly pay rates and a start and end value associated to each. The theory being that your hourly pay is dependent on your takings sitting between the start and end values.

Table Example - dbo.PayScales

PayScaleId Starting Ending HourlyRate
1 0.00 32.88 12.00
2 32.89 34.20 12.50
3 34.21 35.52 13.00

I have the takings stored in a separate table along with a person id, and I need to lookup the hourlyrate based on the takings (which I am having a complete mind block about)

Table Example - dbo.Employees

EmpId Takings HourlyRate
1 33.50
2 31.19
3 37.00

So my exepected results would be:

EmpId 1 Hourly rate = 12.50
EmpId 2 Hourly rate = 12.00
EmpId 3 Hourly rate = 13.00 as the value is greater than the ending value.


You can use CROSS APPLY together with TOP:

FROM dbo.Employees e
    SELECT TOP 1 p.HourlyRate
    FROM dbo.PayScales p
        e.Takings BETWEEN p.Starting AND p.Ending
        OR e.Takings > p.Ending
    ORDER BY p.Ending DESC
) t