Round to the nearest .25

Question

Does anyone know of a way to round to the nearest 0.25 in t-sql? I am currently rounding using

floor(value * 4)/4 

My client is changing his algorithm and wants to make an intermediate round to the nearest quarter. If the value is less than 0.125 rounded to 0.00, if the value is greater than or equal to 0.125, it is rounded to 0.25.

Answer 1

use ROUND(value/25, 2) * 25 as follows:

Example 1:

 DECLARE @value DECIMAL(18, 2) SET @value = 1.126 SELECT CAST(ROUND(@value/25, 2) * 25 as numeric(18,2)) AS rounded_val 

Conclusion:

 1.25 

Example 2:

 DECLARE @value DECIMAL(18, 2) SET @value = 1.124 SELECT CAST(ROUND(@value/25, 2) * 25 as numeric(18,2)) AS rounded_val 

Conclusion:

 1.00 

Answer 2

 select Sample, Round( ( Sample + Sign( Sample ) * 0.125 ) * 4, 0, 1 ) / 4.0 as Rounded from ( values ( 0.0 ), ( 0.1 ), ( 1.125 ), ( 0.25 ), ( 10.5 ), ( -0.75 ), ( -0.875 ), ( -1.12 ), ( -1.125 ) ) as Samples( Sample ) 

Note that ROUND can be used to trim the fractional part of a value regardless of sign. FLOOR will always return a value equal to or less than the original value, which can be problematic when the value is negative.

Answer 3

For those who need to find the closest separator without a remainder, you can use:

 SELECT (CAST(ROUND(@value / 0.25, 2) as int)) * 0.25 

So basically it will be rounded to the nearest multiple of 0.25