How to Floor a number to 0.05 increments in T-SQL

Say you've got a number like 72.26 and you want to round it or floor to the nearest 0.05

The first thing to do is divide it by the round number.  

• For example, if you want 72.26 rounded to the nearest 0.05, this will show how many 0.05's there are in the number 

                72.26 / 0.05 = 1,445.2

                 From there, round or floor as needed

            eg,             

• SELECT ROUND( 72.26 / 0.05  , 0 )   or 
• SELECT FLOOR( 72.26 / 0.05  , 0 )

            Results in 1445.0

Then, multiply it by the round number 

• This returns you to the original value, less then remainder (or to the next increment if it rounded up) 

        eg 

• SELECT 0.05 * ROUND( 72.26 / 0.05  , 0 )  
• Results in 72.25

Demonstration Code 

DECLARE @SourceValues TABLE

(

    [TestValue] DECIMAL(4, 2)

);

DECLARE @Increment DECIMAL(4, 2) = 0.0;

WHILE @Increment <= 0.1

BEGIN

    INSERT INTO @SourceValues

    VALUES

    (72 + @Increment);

    SET @Increment = @Increment + 0.01;

END;

SELECT [TestValue]

  , 0.05 * ROUND([TestValue] / 0.05, 0) [RoundValue]

  , 0.05 * FLOOR([TestValue] / 0.05) [FloorValue]

FROM @SourceValues;

Result

Original Value		Rounded Value		Floor Value
72.00		72.00000000		72.00
72.01		72.00000000		72.00
72.02		72.00000000		72.00
72.03		72.05000000		72.00
72.04		72.05000000		72.00
72.05		72.05000000		72.05
72.06		72.05000000		72.05
72.07		72.05000000		72.05
72.08		72.10000000		72.05
72.09		72.10000000		72.05
72.10		72.10000000		72.10