Rounding - Is there a better way

  • Hi all,

    I'd like some help with a problem I have if I may. Lets say we have the following code;

    DECLARE @VariableA DECIMAL(7,4)

    SELECT @VariableA = 7.0449

    SELECT@VariableA, --Return the source value

    ROUND(@VariableA,2), --Return the value rounded to 2 decimal places

    ROUND(ROUND(@VariableA,3),2) --Return the desired number

    The goal is to round @VariableA to be 7.05 (the third column acheives this), as you can see this is done by my code in 2 stages to acheive the following;

    --Round the last 9 digit to be zero and carry the one over - equalling 7.045

    --Round the 5 up to be zero and again carry the one over - equalling 7.05

    This all works fine providing the number given to me is to 4 decimal places, Im trying to come up with a method that would work for the above example, but also for a number say at 6 decimal places. The obvious thing is for me to loop over it rounding until I get to 2 (either in a while loop or maybe a recursive CTE), but there has to be a better way of acheiving this.

    Any help would be greatfully appreciated.

  • MrT I'm not sure what the final objective is, but this is the first thing i thought of;

    DECLARE @VariableA DECIMAL(7,4)

    SELECT @VariableA = 7.0449

    SELECT CEILING(@VariableA * 100.0)/100.0

    SELECT @VariableA = 7.0001

    SELECT CEILING(@VariableA * 100.0)/100.0

    --results

    7.050000

    7.010000

    but it depends on what you are trying to accomplish...is it "if there are any significant digits, round UP"?

    that's what I'm using the CEILING function to do in my example; is that what we are trying to accomplish?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi,

    Sorry, i didnt explain myself very well did I.

    All I'm trying to do is take a number and round it correctly.

    For example 7.0449 should be returned as 7.05 because I want to round each number in turn, starting on the right, if I just use a ROUND command it returns 7.04 as it is dealing with the 49 as one and rounding down.

    I've got this to work without any issues, however the code I've written is not flexiable enought to round all numbers, for example it can round a source number of 4 decimal places, but wont work with any other number of decimals. (The results should always be to 2 decimal places)

    You method works for the above 7.0449 but wouldnt work for the value 7.033, as it will always round up. The answer to the 7.033 should be 7.03.

    Hope all this makes sense, i'm confusing myself! :hehe:

    Thanks,

  • Aside from the question of 'what makes that correct rounding', considering you are essentially rounding 44.9 up to 50(upwardly skewing your data), there are a few methods you could use for this. However, rounding tends to get pretty convoluted, so I think there are pitfalls with all of them.

    You could use a tally table to walk the decimals backwards and round everything up from right to left... but I'll wait and see if anyone else throws out a better solution before I post that code.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • DECLARE@Sample TABLE

    (

    Col1 DECIMAL(7, 4)

    )

    INSERT@Sample

    SELECT7.0449 UNION ALL

    SELECT7.0444 UNION ALL

    SELECT7.0445 UNION ALL

    SELECT7.033

    SELECTCol1 AS OriginalValue,

    ROUND(ROUND(Col1, 3), 2) AS OriginalPoster,

    ROUND(Col1 + 0.0005, 2) AS Peso

    FROM@Sample


    N 56°04'39.16"
    E 12°55'05.25"

  • I have to agree with what was said.... by whose definition is it that 7.0449 should be rounded to 7.05? Are you intenionally trying to give CPA's a heart attack? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/22/2009)


    I have to agree with what was said.... by whose definition is it that 7.0449 should be rounded to 7.05? Are you intenionally trying to give CPA's a heart attack? 😉

    Hi,

    Sorry, wasnt my intention to scare anyone! 😉

    And I do agree on the point that 7.0449 shouldnt be rounded to 7.05, but the problem was raised to me to as this and whilst I came up with a solution to the specific problem I just wanted to see if anyone had a better way of rounding (the last digits, down to 2 dp).

    Again, sorry. My mistake :hehe:

  • Heh... no problem... I just like to know what drives such requirements. Thanks for the feedback. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • So, if 7.0449 should be rounded up to 7.05, what is the largest value that should be rounded down to 7.04 or should all values > 7.04 be rounded up to 7.05? It's not clear.

    Could you provide more than one example, makes it real difficult to try and work out what you're trying to acheive without sufficient data.

  • Since you will always be rounding to two decimal places, this will work, regardless of the number of decimal positions.

    SELECT ROUND(YourNumber + .005, 2, 0)

  • OP, correct me if I'm wrong here, but let me attempt to explain this better. He's not just trying to round up every time. Here's a couple examples:

    7.0449

    This should round up to 7.05. The reason it should round up to 7.05 (per his request anyways) is that the last 9 rounds the preceding 4 up to a 5. (making your number 7.0450). That 5 then rounds the preceding 4 up to a 5. (Making your number 7.0500). If you were to continue the logic, that number would then become 7.1 and then 7.0, but he wants to stop at 2 decimal places.

    7.0444

    This should round down to 7.04. The reason it should round down to 7.04 (per his request anyways) is that the last digit is a 4, which rounds down to 0. (making you rnumber 7.0440) The next 4 again rounds down to 0. (Making your number 7.0400).

    It is basically 'iterative rounding' staring from your least significant digit.

    The solutions that just add .005 to the number will round both of these up to 7.05

    However, Peso's solution adding .0005 does seem to work in every test I use, assuming you always want this to apply to 2 decimal places.

    [Edit] Formatting.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Garadin (11/23/2009)


    OP, correct me if I'm wrong here, but let me attempt to explain this better:

    He's not just trying to round up every time. Here's a couple examples:

    --His Original Example

    7.0449

    This should round up to 7.05. The *reason* it should round up to 7.05 (per his request anyways) is that the last 9 rounds the preceding 4 up to a 5. (making your number 7.0450). That 5 then rounds the preceding 4 up to a 5. (Making your number 7.0500). If you were to continue the logic, that number would then become 7.1 and then 7.0, but he wants to stop the iterative 'rounding up' at 2 decimal places.

    7.0444

    This should round down to 7.04. The *reason* it should round down to 7.04 (per his request anyways) is that the last digit is a 4, which rounds down to 0. (making you rnumber 7.0440) The next 4 again rounds down to 0. (Making your number 7.0400).

    It is basically 'iterative rounding' staring from your least significant digit.

    The solutions that just add .005 to the number will round both of these up to 7.05

    However, Peso's solution adding .0005 does seem to work in every test I use, assuming you always want this to apply to 2 decimal places.

    [Edit] Formatting.

    Hi,

    Yep your correct, I really messed up explaining this one (sorry :-P). Basically this problem was presented to me and one of the web dev team and we both had to come up with our respective answers to equal the 7.05. I understand this this 'iterative rounding' is different from conventional rounding.

    Really sorry for any confusion!

  • Make sure that your solution is sign-sensitive, because you will now get opposite results for positive and negative numbers

    i.e. +7.044444449 => +7.05

    however -7.044444449 => -7.04

    You're making up your own function (this is NOT rounding, so let's stop calling it that), so you get to decide, but you should make sure to specify what is expected in this case.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 13 posts - 1 through 12 (of 12 total)

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