October 1, 2015 at 8:01 am
I'm baffled here and have no explanation for the devs. They have a numeric created as a type called @usertype and defined as numeric(30,10) in their database. They recently performed a division calculation (all of this @usertype defintion) and received over-rounded results. I advised on using the greater precision on the inputs (i.e., float) and store your result off as a fixed field number of decimals (i.e., the @usertype). That probably won't or can't happen (at least not immediately as this is a live system).
So, I ran the numbers through a few different calcs with both the standard Windows Calculator as my baseline and then some SQL variables and honestly, I cannot explain the differences I see below. I like my float results (that's what I advised) but I cannot explain why numeric(28,10) came back with better results than numeric(30,10).
Take a look below because any insight here would be appreciated. Note: I ran this on SQL 2008 R2 and SQL 2012 SP1.
/*
what the calculator says : 0.000748189381696295
what the calculator says : 0.0007481893|81696295 (bar is line after tenth digit)
*/
declare @numerator float = 8000000
declare @denominator float = 10692480000
SELECT convert(numeric(30,10), @NUMERATOR/@DENOMINATOR) as Result
/*
Result -- GOOD ANSWER. ROUNDS TENTH DIGIT UP BASED ON 11TH DIGIT
---------------------------------------
0.0007481894
*/
GO
declare @numerator numeric(30,10) = 8000000
declare @denominator numeric(30,10) = 10692480000
SELECT convert(numeric(30,10), @NUMERATOR/@DENOMINATOR) as Result
/*
Result -- BAD ANSWER \ WAY TOO MUCH ROUNDING. NOT EVEN SURE WHAT'S UP HERE?
---------------------------------------
0.0007481800
*/
GO
declare @numerator numeric(28,10) = 8000000
declare @denominator numeric(28,10) = 10692480000
SELECT convert(numeric(28,10), @NUMERATOR/@DENOMINATOR) as Result
/*
Result -- GOOD ANSWER BUT DOESN'T ROUND UP THAT 10TH DIGIT BASED ON 11TH DIGIT
---------------------------------------
0.0007481893
*/
GO
October 1, 2015 at 8:22 am
Maybe this will help explain whats going on
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx
October 1, 2015 at 8:33 am
Thanks for the link. I was baffled on this and couldn't explain. This was insightful!
October 1, 2015 at 8:38 am
Yes baffled me too, my Google-foo is on form today, learn something every day
October 1, 2015 at 9:03 am
My google-fu failed me today...so glad yours worked out and thanks for sharing!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy