September 20, 2016 at 12:00 am
Comments posted to this topic are about the item SQL Server Rounding Tips and Tricks
September 20, 2016 at 1:25 am
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:
September 20, 2016 at 4:16 am
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.
September 20, 2016 at 6:45 am
Nice article. Good refresher course.
September 20, 2016 at 7:29 am
John McC (9/20/2016)
The Conditional rounding is not giving the desired results, based on the statementpeople 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.
September 20, 2016 at 10:26 am
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)
September 20, 2016 at 10:58 am
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.
September 23, 2016 at 7:55 am
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.
September 23, 2016 at 8:49 am
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.
September 23, 2016 at 9:09 am
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.
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.
September 23, 2016 at 9:19 am
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.
September 23, 2016 at 9:34 am
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.
September 23, 2016 at 2:00 pm
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.
----------------------------------------------------
April 17, 2020 at 5:23 pm
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