## Rounding Tips and Tricks

 Author Message Luis Cazares SSC Guru Group: General Forum Members Points: 167509 Visits: 22857 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 Hall of Fame Group: General Forum Members Points: 3230 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.000For 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. dav0id SSC-Enthusiastic Group: General Forum Members Points: 124 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 SSC Guru Group: General Forum Members Points: 68614 Visits: 886 Nice article. Good refresher course. Luis Cazares SSC Guru Group: General Forum Members Points: 167509 Visits: 22857 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.000For the result to round down, -2.890 should be -3.000 and -2.500 should be -3.000I'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 SSC Rookie Group: General Forum Members Points: 28 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 -2It 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 SSC Guru Group: General Forum Members Points: 167509 Visits: 22857 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 -2It 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 SSC Journeyman Group: General Forum Members Points: 80 Visits: 66 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 #DecimalsVALUES (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) SimulatingCeiling2FROM #Decimals;`I got these results instead:OriginalValue SimulatingFloor SimulatingCeiling SimulatingFloor2 SimulatingCeiling23.2300 0 10 0 103.7600 0 10 0 103.1500 0 10 0 103.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 0What am I missing?Please clarify.Thank you. levenyu SSC Journeyman Group: General Forum Members Points: 80 Visits: 66 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 #DecimalsVALUES (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) SimulatingCeiling2FROM #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 SSC Guru Group: General Forum Members Points: 167509 Visits: 22857 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