## Five percent rounded down

 Author Message mhtanner SSC Veteran Group: General Forum Members Points: 298 Visits: 187 Comments posted to this topic are about the item Five percent rounded down kapil_kk SSChampion Group: General Forum Members Points: 12867 Visits: 2779 god start with a coffee +2 :-) _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/ Raghavendra Mudugal SSCertifiable Group: General Forum Members Points: 6464 Visits: 2958 Very interesting, thank you for the question.(and Happy Dasara to all) ww; Raghu--The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. Tom Thomson SSC Guru Group: General Forum Members Points: 59853 Visits: 13181 Nice question.The trick of adding 0.000000001 to the multiplicand might work sometimes, but it isn't the best way to handle this.Until someone introduces proper modern floating point into SQL (which would eliminate the problem completely) the way to handle this is to use round, which means going via numeric. So in this case, change the calculation for Quota3 to be either select @Quota3 = round(@Value*cast(1.05 as numeric(5,2)) ,0);or select @Quota3 = round(cast((cast(@Value as float) * 1.05) as numeric(38,27)),0);and the problem goes away. (and of course there should be similar changes in the calculations of the other two numbers).Note that it is necessary, in the second version, to change from using real to using float if you want it to work whatever your integer is. Real (float(24)) is not a sensible type to use for this, since it can't represent all 32 bit integers; so it makes sense to change real to float, which can, as well as using round.But proper modern floating point would be much much better. It's about time we were hearing something about it for SQL Standards, as it's now 50 months since IEEE 754-2008 was published. Tom Michael Riemer Hall of Fame Group: General Forum Members Points: 3871 Visits: 691 Thanks for the question, it is a great point to learn from! I hadn't seen this before, but know there are always issues with real and float numbers so had to check, and the title kind of gave me a clue that something wouldn't be right.I guess you only really know this if it has bitten you already! Koen Verbeeck SSC Guru Group: General Forum Members Points: 177683 Visits: 13357 Interesting question, thanks.I do wonder how people are supposed to get this right without running the script. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP palotaiarpad Hall of Fame Group: General Forum Members Points: 3344 Visits: 870 Lets play a bit:`Select cast(100 as real) * 1.05,cast(cast(100 as real) * 1.05 as int);-- Result is 105, 104`The result is very strange for me. Does SQL Server the calculation from inside to outside or not? Raghavendra Mudugal SSCertifiable Group: General Forum Members Points: 6464 Visits: 2958 palotaiarpad (10/24/2012)Lets play a bit:`Select cast(100 as real) * 1.05,cast(cast(100 as real) * 1.05 as int);-- Result is 105, 104`The result is very strange for me. Does SQL Server the calculation from inside to outside or not? ...change the second "real" to "numeric" it gives same as 105...(as it says in BOL "Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. ") ww; Raghu--The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. malleswarareddy_m SSCarpal Tunnel Group: General Forum Members Points: 4217 Visits: 1189 Nice question. MalleswarareddyI.T.AnalystMCITP(70-451) palotaiarpad Hall of Fame Group: General Forum Members Points: 3344 Visits: 870 My problem is, that the first expression evaluates to 105, and if i cast the 105 to int it should remain 105 and not less.