Search code examples
sqlsql-serverselect

SQL Get Numeric Value Nearest Rounded Off from Input Value

I have table named Tab_SqftAdjustment and has columns Sqft ,Adjustment_Factor. I need to get the value of Adjustment_Factor based on the nearest rounded off from my input. For example that table Sqft column has data of 400, 500, 600, and Adjustment_Factor has 1.41 and 1.33, 1.25. For example my input is 410 Sqft. I should get 1.41 since 410 when rounded is near to 400. If my input is 450 I should get 1.33 since 450 is practically near to 500. I have issue when my input is 550. It gives me 1.33 which is 500 SQFT. I should get 1.25 which is 600 SQFT.

This is my sample SQL

select top 1 Sqft,Adjustment_Factor FROM Tab_SqftAdjustment ORDER BY ABS(sqft - 550)

Please see attached photos.

Table data:
Table data

Input 410 (correct):
input: 410

Input 450 (correct):
input: 450

Input 550 (incorrect):
input:550

select top 1 Sqft,Adjustment_Factor FROM Tab_SqftAdjustment ORDER BY ABS(sqft - 410)
select top 1 Sqft,Adjustment_Factor FROM Tab_SqftAdjustment ORDER BY ABS(sqft - 450)
select top 1 Sqft,Adjustment_Factor FROM Tab_SqftAdjustment ORDER BY ABS(sqft - 550)
Solution

  • 550 is the same "distance" from 500 and 600. You can add another term to the order by clause to prefer the higher value:

    SELECT   TOP 1 Sqft, Adjustment_Factor 
FROM     Tab_SqftAdjustment 
ORDER BY ABS(sqft - 550) ASC, sqft DESC 
-- Here ----------------------^