SQL Server Rounding Tips and Tricks

  • Luis Cazares

    SSC Guru

    Points: 183633

    Comments posted to this topic are about the item SQL Server Rounding Tips and Tricks

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • John McC

    Hall of Fame

    Points: 3410

    The Conditional rounding is not giving the desired results, based on the statement

    people need to round only if it would round down.

    That would imply an OriginalValue greater than or equal to the ConditionalRound. The positive values are correct however the negative values, when rounded, round up.

    -2.340 < -2.000

    -2.890 = -2.890

    -2.250 < -2.000

    -2.500 = -2.500

    -2.200 < -2.000

    For the result to round down, -2.890 should be -3.000 and -2.500 should be -3.000

    -------------------------------------------------------------------------
    Normal chaos will be resumed as soon as possible. :crazy:

  • dav0id

    SSC Enthusiast

    Points: 136

    Thanks for this excellent article - very clearly explained with good, step-by-step examples.

    I kind of already knew this, but it's nice to have a reference so clearly written up.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice article. Good refresher course.

  • Luis Cazares

    SSC Guru

    Points: 183633

    John McC (9/20/2016)


    The Conditional rounding is not giving the desired results, based on the statement

    people need to round only if it would round down.

    That would imply an OriginalValue greater than or equal to the ConditionalRound. The positive values are correct however the negative values, when rounded, round up.

    -2.340 < -2.000

    -2.890 = -2.890

    -2.250 < -2.000

    -2.500 = -2.500

    -2.200 < -2.000

    For the result to round down, -2.890 should be -3.000 and -2.500 should be -3.000

    I'm sorry, I got carried away because the original problem was meant only for positive numbers and the solution is meant to round only if it would round towards zero. This is meant to be an example, I'm not even sure what's the real use for this, but it was asked in the forums.

    Thank you for pointing this out.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Braiden Jones

    SSC Rookie

    Points: 38

    Hello,

    I was a bit confused by a section in the article where it's stated that,

    "Note that when dealing with negative numbers, CEILING will continue to go away from zero. E.g. CEILING(-2.3) will return -3."

    Looking at the results it doesn't seem to be the case:

    OriginalValue UsingCeiling

    --------------- --------------

    3.230 4

    3.760 4

    3.150 4

    3.500 4

    -2.340 -2

    -2.890 -2

    -2.250 -2

    -2.500 -2

    -2.200 -2

    It seems that when dealing with negative numbers, CEILING will go towards zero. Is this a typo or am I understanding it incorrectly? (Sorry for the ugly formatting)

  • Luis Cazares

    SSC Guru

    Points: 183633

    braidensjones (9/20/2016)


    Hello,

    I was a bit confused by a section in the article where it's stated that,

    "Note that when dealing with negative numbers, CEILING will continue to go away from zero. E.g. CEILING(-2.3) will return -3."

    Looking at the results it doesn't seem to be the case:

    OriginalValue UsingCeiling

    --------------- --------------

    3.230 4

    3.760 4

    3.150 4

    3.500 4

    -2.340 -2

    -2.890 -2

    -2.250 -2

    -2.500 -2

    -2.200 -2

    It seems that when dealing with negative numbers, CEILING will go towards zero. Is this a typo or am I understanding it incorrectly? (Sorry for the ugly formatting)

    I'm so sorry, that was a case of phat fingering. It's supposed to say -2. The example shows the correct behavior and the simulation copies that behavior.

    I apologize for the confusion, I've submitted a correction to address both remarks made so far.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • levenyu

    SSC Journeyman

    Points: 94

    Hi Luis,

    RE: "Simulating CEILING and FLOOR with different lengths"

    The following did not return expected results (i.e. the results you provided in your article).

    IF object_id('tempdb..#Decimals', 'U') IS NOT NULL

    DROP TABLE #Decimals;

    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)

    ;

    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;

    I got these results instead:

    OriginalValue SimulatingFloor SimulatingCeiling SimulatingFloor2 SimulatingCeiling2

    3.2300 0 10 0 10

    3.7600 0 10 0 10

    3.1500 0 10 0 10

    3.5000 0 10 0 10

    -2.3400 -10 0 -10 0

    -2.8900 -10 0 -10 0

    -2.2500 -10 0 -10 0

    -2.5000 -10 0 -10 0

    -2.2000 -10 0 -10 0

    What am I missing?

    Please clarify.

    Thank you.

  • levenyu

    SSC Journeyman

    Points: 94

    Hi Luis,

    here is the script which returns results as you've posted it:

    IF object_id('tempdb..#Decimals', 'U') IS NOT NULL

    DROP TABLE #Decimals;

    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);

    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;

    I am not sure why you've used @Length int = -1, but it would be better to have it = 1 and thus there would be no need to reverse its sign in the formulas used.

    Regards.

  • Luis Cazares

    SSC Guru

    Points: 183633

    Actually, the formula is working as intended. The problem is that I was testing the formula with different values for the length and forgot to set it back when copying it to the article.

    I used the word length because it's the one used in BOL for the ROUND() function documentation.

    From BOL


    Is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • levenyu

    SSC Journeyman

    Points: 94

    Luis,

    Thank you for the reply.

    There is no issue with using word Length in there, it was that (-1) value of the @Length variable, which has caused wrong results to be rendered.

    Thanks.

  • Luis Cazares

    SSC Guru

    Points: 183633

    levenyu (9/23/2016)


    Luis,

    Thank you for the reply.

    There is no issue with using word Length in there, it was that (-1) value of the @Length variable, which has caused wrong results to be rendered.

    Thanks.

    I was just trying to make sure that I made myself clear. If one person got confused, maybe others could be confused as well.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • MMartin1

    One Orange Chip

    Points: 27502

    Good article that highlights rounding as really a formatting function. In the movement and derivation of data, I like to save rounding towards the end. That is, when you actually deliver a report to a client and base the rounding on particular likes. But the true value is always preserved, for as mentioned, rounding inputs rather than the output more likely than not will result in a untrue value.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • aforsythe

    Valued Member

    Points: 54

    Hi Luis,

    I see that you fixed the rounding example for CEILING, but your comment is still wrong:

     

    "Note that when dealing with negative numbers, CEILING will continue to go away from zero. E.g. CEILING(-2.3) will return -2."

    CEILING isn't going away from zero, it's still rounding to a higher number which is towards zero when dealing with negative numbers.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply