## Five percent rounded down

 Author Message mhtanner SSC-Enthusiastic Group: General Forum Members Points: 103 Visits: 136 Comments posted to this topic are about the item Five percent rounded down kapil_kk SSCertifiable Group: General Forum Members Points: 5392 Visits: 2767 god start with a coffee +2 :-) _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/ Raghavendra Mudugal Hall of Fame Group: General Forum Members Points: 3380 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 One Orange Chip Group: General Forum Members Points: 26191 Visits: 12505 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: 3116 Visits: 656 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: 63596 Visits: 13298 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 SSCrazy Group: General Forum Members Points: 2432 Visits: 804 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 Hall of Fame Group: General Forum Members Points: 3380 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 SSCrazy Group: General Forum Members Points: 2759 Visits: 1189 Nice question. MalleswarareddyI.T.AnalystMCITP(70-451) palotaiarpad SSCrazy Group: General Forum Members Points: 2432 Visits: 804 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.