SQL Server Rounding Tips and Tricks

  • 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
  • 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:

  • 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.

  • Nice article. Good refresher course.

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

  • 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
  • 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:

    OriginalValueSimulatingFloorSimulatingCeilingSimulatingFloor2SimulatingCeiling2

    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.

  • 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.

  • 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
  • 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.

  • 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
  • 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.

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

  • 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 13 (of 13 total)

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