Rounding tips

CREATE TABLE #Decimals(
    OriginalValue   decimal(10,4)
);

INSERT INTO #Decimals
VALUES
    (3.23),
    (3.76),
    (3.15),
    (3.5),
    (-2.34),
    (-2.89),
    (-2.25),
    (-2.5) ,
    (-2.2)
;

-- ROUND ( numeric_expression , length [ ,function ] )

SELECT OriginalValue,
    ROUND(OriginalValue, 0) UsingRound,
    ROUND(OriginalValue, 0, 1) UsingRoundWithTruncate,
    FLOOR(OriginalValue)    UsingFloor,
    CEILING(OriginalValue)  UsingCeiling
FROM #Decimals;

 

-- Simulating CEILING and FLOOR with different lengths

DECLARE @Length int = -1,
    @10 float = 10;

SELECT OriginalValue,
    --1st Option
    FLOOR(  OriginalValue*POWER(@10,@Length))/POWER(@10,@Length)  SimulatingFloor,
    CEILING(OriginalValue*POWER(@10,@Length))/POWER(@10,@Length)  SimulatingCeiling,
    --2nd Option
    ROUND(OriginalValue-(.49*POWER(@10,-@Length)),@Length)    SimulatingFloor2,
    ROUND(OriginalValue+(.49*POWER(@10,-@Length)),@Length)  SimulatingCeiling2
FROM #Decimals;

 

-- Conditional rounding

SELECT OriginalValue,
    CASE WHEN ROUND(OriginalValue, 0) = ROUND(OriginalValue, 0, 1)
            THEN ROUND(OriginalValue, 0)
            ELSE OriginalValue END
FROM #Decimals;

 

-- Get the decimal part of a number

SELECT OriginalValue,
    OriginalValue - ROUND(OriginalValue, 0) RoundingDifference,
    OriginalValue - ROUND(OriginalValue, 0, 1) DecimalPart
FROM #Decimals;

-- Cautions when rounding and aggregating

SELECT SUM(OriginalValue) OriginalValueSUM,
    ROUND(SUM(OriginalValue), 0) RoundAfterAggregation,
    SUM(ROUND(OriginalValue, 0)) RoundBeforeAggregation
FROM #Decimals;