Round to the closest .25

Question

Does anyone know of a way to round to the closest .25 in t-sql? Currently I am rounding down using

floor(value * 4)/4

My client is changing their algorithm and wants to do a midpoint round to the closest quarter. If the value is less than .125 round to 0.00, if the value is greater than or equal to .125 round up to .25.

Answer 1

I needed to round to an arbitrary precision & rounding strategy.

rounding to *.25

DECLARE @roundamt DECIMAL(16,2) = .25, @value DECIMAL(18, 2) = 1.7688
SELECT  ROUND(  (@value - FLOOR(@value)) / @roundamt, 0 ) * @roundamt + FLOOR(@value)

-- result = 1.750000

different rounding strategies like 0.02, 0.25, 0.5, 0.75

SELECT
    x.roundamt, ROUND(  (@value - FLOOR(@value)) / roundamt, 0 ) * roundamt + FLOOR(@value)
FROM   
    ( VALUES (0.02), (0.25), (0.5), (0.75) )x(roundamt)

Answer 2

use ROUND(value/25, 2) * 25 like this:

Example1:

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

Output:

1.25

Example2:

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

Output:

1.00

Answer 3

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 truncate the fractional part of a value regardless of the sign. FLOOR will always return a value equal to or less than the original value, which can be problematic when the value is negative.