November 22, 2009 at 5:20 am
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.
November 22, 2009 at 6:32 am
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
November 22, 2009 at 6:52 am
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,
November 22, 2009 at 9:48 am
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.
November 22, 2009 at 1:17 pm
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"
November 22, 2009 at 5:52 pm
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
Change is inevitable... Change for the better is not.
November 23, 2009 at 1:03 am
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:
November 23, 2009 at 7:57 am
Heh... no problem... I just like to know what drives such requirements. Thanks for the feedback. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2009 at 9:12 am
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.
November 23, 2009 at 9:17 am
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)
November 23, 2009 at 9:28 am
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.
November 23, 2009 at 9:34 am
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!
November 23, 2009 at 10:25 am
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