Stuart1044 - 4 months ago 6x
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`:

``````SELECT *
FROM dbo.Employees e
CROSS APPLY(
SELECT TOP 1 p.HourlyRate
FROM dbo.PayScales p
WHERE
e.Takings BETWEEN p.Starting AND p.Ending
OR e.Takings > p.Ending
ORDER BY p.Ending DESC
) t
``````

ONLINE DEMO

Source (Stackoverflow)