Click here to Skip to main content
15,886,003 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an application that user define formula based on other columns (e.g c=(a-b)*100/a ) then the application create SQL update query to calculate result (Types of all columns are NUMERIC). When the formula contains multiplication and division the result is not accurate. I know SQL Server truncates the scale to fit the result in multiplication and division.

http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server
https://blogs.msdn.microsoft.com/sqlprogrammability/2006/03/29/multiplication-and-division-with-numerics/

But I am confused with below test. When I use variable or use formula in Select query the result is accurate but when I use formula in Update query the result is inaccurate!

<pre lang="SQL">--USE NUMERIC
CREATE TABLE TblTest
(
    a [numeric](18, 8) NULL,
    b [numeric](28, 15) NULL,
    c [numeric](28, 15) NULL    
) ON [PRIMARY]

INSERT TblTest (a, b)
VALUES (158.62700000, 155.000000000000000)

UPDATE TBlTest
SET c = (a - b) * 100 / a

SELECT a, b, c, (a-b) * 100 / a AS c2 
FROM TBlTest 

--Result: c=2.286495000000000 !!! and c2=2.286495993746

DECLARE @a AS NUMERIC(18, 8) = 158.62700000
DECLARE @b AS NUMERIC(28, 15) = 155.000000000000000
DECLARE @c AS NUMERIC(28, 15)

SELECT (@a - @b) * 100 / @a    

--Result: 2.286495993746

SELECT @c = (@a - @b) * 100 / @a 

--Result: 2.286495993746000

--USE FLOAT
CREATE TABLE TblTest2
(   
    a FLOAT(53) NULL,
    b FLOAT(53) NULL,
    c FLOAT(53) NULL    
) ON [PRIMARY]

INSERT TblTest2 (a, b)
VALUES (158.62700000, 155.000000000000000)

UPDATE TBlTest2
SET c = (a - b) * 100 / a

SELECT a, b, c, (a - b) * 100 / a AS c2 
FROM TBlTest2 

--Result: 2.28649599374634


What I have tried:

I test CLR function for division numeric types and the result was satisfactory but it is not suitable for my application.
Posted
Updated 18-Oct-16 20:31pm
Comments
Tomas Takac 19-Oct-16 5:39am    
Multiplication and divisions with numeric/decimal can be tricky because of the scale & precision alignments. I don't have time to give you a proper answer right now but what you can try is to convert to FLOAT do the calc then convert back to numeric.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900