CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Fergus
Fergus

Reputation: 2982

SQL - Retrieve Closest Lower Value

When a column value does not equal, I would like to retrieve the closest lower pay value.

For instance: 10 yearsOfService should equal the value 650.00; 14 yearsOfService would equal the value 840.00 in the below incentive table, 

ID   Pay     yearsOfService 
1   125.00      0
2   156.00      2
3   188.00      3
4   206.00      4
5   650.00      6
6   840.00      14
7   585.00      22
8   495.00      23
9   385.00      24
10  250.00      25

I have tried several different approaches; including:

          SELECT TOP 1 (pay) as incentivePay
          FROM incentive
          WHERE yearsOfService = '10'

This works but only for yearsOfService that match.

With 10 yearsOfService:

RESULTSET = [1  650.00]

Any ideas?

Upvotes: 0

Views: 163

Answers (1)

TechDo
TechDo

Reputation: 18629

Please try:

SELECT TOP 1 (pay) as incentivePay
FROM incentive
WHERE yearsOfService <= '10'
ORDER BY yearsOfService desc

Upvotes: 3

Related Questions