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:
Input 410 (correct):
Input 450 (correct):
Input 550 (incorrect):
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 ----------------------^
