CODEWITHSUNDEEP
sqlsql-server-2008
Stuart1044
Stuart1044

Reputation: 444

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.

Upvotes: 0

Views: 44

Answers (2)

dnoeth
dnoeth

Reputation: 60482

Of course @FelixPamittan's answer solves the problem, but with a small change in your data it's down to a simple join.

Change the highest Ending to a really high value (999999999), greater than any Takings, or NULL:

FROM #Employees AS e
JOIN #PayScales AS p
ON e.Takings BETWEEN p.Starting AND p.Ending
-- or
ON e.Takings BETWEEN p.Starting AND COALESCE(p.Ending, 999999999)

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 2

Related Questions