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