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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply