Rounding to a factor in Transact SQL
Straight port from VBA routines written by Dejan Mladenovic.
Very useful to round to a quarter of a day for example. The following query should yield 12.75:
SELECT dbo.FN_RoundNear(12.63, 0.25)
You can download the script here.
<french>
Portage vers SQL Server d’un code VBA qui permet d’arrondir un nombre à un multiple donné. C’est le genre de truc qu’on faisait au collège en 6ème, et pourtant c’est bien pratique de le trouver pré-mâché!
P.S.: la syntaxe de TSQL est vraiment laide à pleurer.
</french>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION FN_RoundUpDown
(
@nb FLOAT,
@factor FLOAT
)
RETURNS FLOAT
AS
BEGIN
-- ***********
-- Name: FN_RoundUpDown (Function)
-- Purpose: round @nb to @factor, up or down
-- Inputs: @nb = number to round
-- @factor = rounding precision
-- +@factor = rounds UP
-- -@factor = rounds DOWN
-- Example: FN_RoundUpDown(5.12,+0.25) = 5.25
-- FN_RoundUpDown(5.12,-0.25) = 5.00
-- Output: @nb rounded UP/DOWN
-- ***********
DECLARE @result FLOAT
DECLARE @temp_nb FLOAT
SET @temp_nb = @nb / @factor
IF (FLOOR(@temp_nb) - @temp_nb) = 0
SET @result = @nb
Else
SET @result = FLOOR((((@nb + (2 * @factor)) / @factor) - 1)) * @factor
RETURN @result
END
GO
CREATE FUNCTION FN_RoundNear
(
@nb FLOAT,
@factor FLOAT
)
RETURNS FLOAT
AS
BEGIN
-- ***********
-- Name: FN_RoundNear (Function)
-- Purpose: rounds @nb to the nearest fraction equal
-- @factor
-- Inputs: @nb - number to round
-- @factor - the fraction used as measure of
-- rounding
-- Example: FN_RoundNear(53,6) = 54
-- FN_RoundNear(1.16,0.25) = 1.25
-- FN_RoundNear(1.12,0.25) = 1.00
-- FN_RoundNear(1.125,0.25)= 1.25
-- Output: @nb rounded to nearest
-- multiple of @factor.
-- ***********
DECLARE @result FLOAT
DECLARE @decValue FLOAT
DECLARE @intX INT
DECLARE @divX FLOAT
SET @divX = @nb / @factor
SET @intX = FLOOR(@divX)
SET @decValue = @divX - @intX
IF @decValue >= 0.5
SET @result = @factor * (@intX + 1)
Else
SET @result = @factor * @intX
RETURN @result
END
GO