Rounding - Is there a better way

Question

Post reply

Rounding - Is there a better way

NicHopper

SSCrazy Eights

Points: 9090
More actions
November 22, 2009 at 5:20 am

#139585

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.
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 2

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,

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Garadin One Orange Chip Points: 29613 More actions · Answer 3

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]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

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"

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

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)

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 6

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:

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

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)

nigel. SSChampion Points: 11636 More actions · Answer 8

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.

Nigel
How to post questions

PhilPacha Hall of Fame Points: 3645 More actions · Answer 9

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)

Garadin One Orange Chip Points: 29613 More actions · Answer 10

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]

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 11

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!

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

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?