Strange rounding issues

Question

Strange rounding issues

Jason-299789

SSC-Insane

Points: 21601
More actions
February 18, 2015 at 5:02 am

#301268

So heres the problem, I work in the Insurance market, so theres a lot of multiplication and division going on.
We've run across a particularly knotty issue
DECLARE
@var1 as DECIMAL(18,10)=55.55000000000000
,@var2 as DECIMAL(18,10)=0.47500000000000
,@var3 as DECIMAL(18,10)=100.00000000000000
SELECT @var1/100.00*@Var2/100.00*@var3/100.00
SELECT (@var1/100.00)*(@Var2/100.00)*(@var3/100.00)
SELECT (@var1*@Var2*@var3)/1000000.00
In the first case it the number is rounded to 6 DP's, so I thought it was some form of solve order.
In the second I decided to isolate the divisions then do the calculations, just in case, the same results, but with a minor rounding.
In the final case I removed the entire Division, did the multiplication followed by the a division of 100^3, and get the correct number.
When dealing with Premiums sub 100,000 its not a significant issue but in the insurance industry we can have premiums in to the billions, and it becomes a material issue in terms of accurate reporting of premiums, sums insured and Exposure levels.
I have the work round but I'd like to know why SQL behaves in this way. when logically it shouldn't.
_________________________________________________________________________
SSC Guide to Posting and Best Practices

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

Now check this:

SELECT CAST(@var1/100.00 AS DECIMAL(18,10))*CAST(@Var2/100.00 AS DECIMAL(18,10))*CAST (@var3/100.00 AS DECIMAL (18,10))

Look here

The precision and scale is changing with each one of your calculations, as they have maximums. So I believe what is happening is you start running against the max precision, and it starts cutting back the scale because it can't increase the precision.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

Ok so I've spent lunch investigating and it seems that the issue is a little deeper than first thought.

In the example below, using the same values as the opening post

DECLARE

@var1 as DECIMAL(18,10)=55.55000000000000

,@var2 as DECIMAL(18,10)=0.47500000000000

,@var3 as DECIMAL(18,10)=100.00000000000000

SELECT (@var1/100.00)*(@Var2/100.00)*(@var3/100.00)

SELECT (@var1/100)*(@Var2/100)*(@var3/100)

I think I've entered a parallel universe, or gone completely mad...... :unsure:

Neyvn, Thanks for that I was sure I'd tried a convert/cast and got the results to 6 DP, might be the order of the convert.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 3

The precision and scale of the result are calculated ( see https://msdn.microsoft.com/en-us/library/ms190476.aspx

look especially as this comment:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

@var1 / 100.00 => decimal(26, 16)

@var1 / 100.00 * @Var2 / 100.00 => decimal(38,17)..

@var1 / 100.00 * @Var2 / 100.00 * @Var3 / 100.00 => decimal(38,6) , the scale is reduced.

@var1 * @var2 * @var3 => decimal(38,10)

Louis.

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 4

I posted a QotD about this a while ago

http://www.sqlservercentral.com/questions/T-SQL/74721/

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

Mark Cowne (2/18/2015)
I posted a QotD about this a while ago
http://www.sqlservercentral.com/questions/T-SQL/74721/

I actually answered that QotD and got it right. :hehe:

It makes sense for there to be truncation to prevent data loss but in doing so you still suffer data loss.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Nevyn SSChampion Points: 14210 More actions · Answer 6

Jason-299789 (2/18/2015)
I actually answered that QotD and got it right. :hehe:
It makes sense for there to be truncation to prevent data loss but in doing so you still suffer data loss.

Its more that the system assumes it is losing data regardless and would rather be off by the rightmost number than the leftmost.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

I understand now its been explained, I'm still mystified why it cant do the calculation then look at the right side then fill the left side with what's left, rather than the arbitrary 6 DP truncation.

I've got the workround in place so we'll see what happens, in the main its only affecting numbers that are significantly large enough, even then its within a degree of acceptable variance.

_________________________________________________________________________
SSC Guide to Posting and Best Practices