Log in  ::  Register  ::  Not logged in

## What's wrong in this .. simple calculations

 Author Message diveshps Forum Newbie Group: General Forum Members Points: 2 Visits: 5 If I run the following on SQL Server 2012 I get two different answers from the first two select statments.Any reason why and what I need to do to avoid this. The second statement gives the wrong answer and that is the statement that I need to use in my code. With the change in statement 3 , I get the right answer but I am not getting the confidence that I should introduce the change in all my calculations.select round((1400.05 / 4232.33 ) * 4180.06 ,2) --Statement 1declare @v1 smallmoney = 1400.05, @v2 smallmoney = 4232.33, @v3 smallmoney = 4180.06select round((@v1 / @v2 ) * @v3 ,2) --Statement 2select round((@v1*1.0 / @v2 ) * @v3 ,2) --Statement 3Thank you very much for the help.RegardsDivesh laurie-789651 SSC-Addicted Group: General Forum Members Points: 424 Visits: 1271 I think the intermediate result from @v1 / @v2 is being truncated because you're using smallmoney data type.Can you use a different datatype, or cast one value to decimal to make it clearer what's happening.e.g.`select round((CAST(@v1 as decimal(18,2)) / @v2 ) * @v3 ,2) --Statement 2`or`declare @v1 smallmoney = 1400.05, @v2 smallmoney = 4232.33, @v3 smallmoney = 4180.06select round((CAST(@v1 as decimal(18,2)) / @v2 ) * @v3 ,2), --Statement 2 CAST(@v1 as decimal(18,2)) / @v2, CAST(round((CAST(@v1 as decimal(18,2)) / @v2 ) * @v3 ,2) as SmallMoney) -- <----<<<` diveshps Forum Newbie Group: General Forum Members Points: 2 Visits: 5 Laurie,Thank you for your reply.I guess if I force a cast to a decimal it will work since that is what happens when I add the "* 1.0" but I thought money or smallmoney was a decimal number.Even if I change the datatype to money, I get the same results.If we have to force a cast or conversion to a decimal type then does it mean that there is no point in using the money or smallmoney data type? !!! I hope this is not the conclusion of this. :-)Thanks for any more leads on this.RegardsDivesh laurie-789651 SSC-Addicted Group: General Forum Members Points: 424 Visits: 1271 Money & Smallmoney both have 4 decimal places, so that's why they lose the detail. If you're looking for accuracy it's better to use decimal.It's all a bit of a minefield. Ray M UDP Broadcaster Group: General Forum Members Points: 1491 Visits: 1076 Discusses Issues with money.There are rounding issues with money and is probably not the best datatype to use in your scenario.[url=http://www.sqlservercentral.com/Forums/Topic544518-9-1.aspx][/url] cphite SSC-Enthusiastic Group: General Forum Members Points: 140 Visits: 650 diveshps (9/27/2012)Laurie,Thank you for your reply.I guess if I force a cast to a decimal it will work since that is what happens when I add the "* 1.0" but I thought money or smallmoney was a decimal number.Even if I change the datatype to money, I get the same results.If we have to force a cast or conversion to a decimal type then does it mean that there is no point in using the money or smallmoney data type? !!! I hope this is not the conclusion of this. :-)Thanks for any more leads on this.RegardsDiveshSpeaking for myself, I always use DECIMAL to store dollar amounts to avoid exactly this sort of issue. I've never seen the point of money or smallmoney. Evil Kraig F SSCertifiable Group: General Forum Members Points: 5715 Visits: 7660 cphite (9/27/2012)Speaking for myself, I always use DECIMAL to store dollar amounts to avoid exactly this sort of issue. I've never seen the point of money or smallmoney.Backwards compatibility. - Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA Sean Pearce Ten Centuries Group: General Forum Members Points: 1170 Visits: 3432 This behaviour is due to data type precedence.(@v1 / @v2 ) * @v3 will return smallmoney((@v1 * 1.0) / @v2 ) * @v3 will return numericThis can be demonstrated like this:DECLARE @v1 SMALLMONEY = 1400.05, @v2 SMALLMONEY = 4232.33;SELECT @v1 / @v2 AS Val INTO TestTable1SELECT @v1 / @v2 * 1.0 AS Val INTO TestTable2EXEC sp_help TestTable1EXEC sp_help TestTable2DROP TABLE TestTable1DROP TABLE TestTable2And you will see the following:TestTable1Name - ValType - smallmoneyLength - 4Precision - 10Scale - 4TestTable 2Name - ValType - numericLength - 9Precision - 13Scale - 5 The SQL Guy @ blogspot@SeanPearceSQLAbout Me Sergiy SSCertifiable Group: General Forum Members Points: 5852 Visits: 11412 YOu may folow the money by tracing intermediate results:`select (1400.05 / 4232.33 ), (1400.05 / 4232.33 ) * 4180.06, round((1400.05 / 4232.33 ) * 4180.06 ,2) --Statement 1declare @v1 smallmoney = 1400.05,@v2 smallmoney = 4232.33,@v3 smallmoney = 4180.06select @v1, (@v1 / @v2 ), (@v1 / @v2 ) * @v3, round((@v1 / @v2 ) * @v3 ,2) --Statement 2select @v1*1.0, (@v1*1.0 / @v2 ) ,(@v1*1.0 / @v2 ) * @v3 , round((@v1*1.0 / @v2 ) * @v3 ,2) --Statement 3`For most accurate calculations always use FLOAT data type (unless you need to handle more than 15 digigts, than use "big" decimals).Try use FLOAT in your example and you'll see that 4 digit returned by DECIMAL calculation is incorrect.Fortunateluy for you, it's beyond rounding precision.