SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rounding Tips and Tricks


Rounding Tips and Tricks

Author
Message
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158156 Visits: 22523
Comments posted to this topic are about the item 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
John McC
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3092 Visits: 1678
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
dav0id
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 343
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.
Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63154 Visits: 886
Nice article. Good refresher course.
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158156 Visits: 22523
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
Braiden Jones
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 11
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
Luis Cazares
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158156 Visits: 22523
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
levenyu
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 65
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
levenyu
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 65
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
Luis Cazares
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158156 Visits: 22523
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search