CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
sudhansu63
sudhansu63

Reputation: 6190

SQL Server rounding Error, Giving different values

I have a Stored Procedure that does a lots of calculation, stores the results in several temporary table. Finally calculating the sum and rounding to two decimal and stores in a temporary table and selects that.

All the intermediate and final temporary table has datatype float for the column of concern.

original Scenario:

Declare @Intermediate table
{
 --several other columns

Labor float

--several other columns
};

---Lots of calculation ---xx-----

Declare @Final table
{
 --several other columns

LaborTotal float

--several other columns
};

INSERT INTO @Final  SELECT ROUND(ISNULL((SELECT SUM([Labor]) FROM @Intermediate ),0),2)  AS LaborTotal;

SELECT * FROM @Final;

Result: 7585.22  --> when rounded  //Here is the error Expecting 7585.23
        7585.225 --> when not rounded

TestCases :

   DECLARE @test float = 7585.225;
   SELECT ROUND(@test,2) AS Result; --> results 7585.23

   SELECT ROUND(7585.225,2) AS Result --> results 7585.23

Inserted individual values to a temporary table, and then calculated the sum

DECLARE @TmpTable table
(
 MaterialAmount float
 ,LaborAmount float
);

INSERT INTO @TmpTable VALUES (12.10,1218.75);
INSERT INTO @TmpTable VALUES (12.10,1090.125);
INSERT INTO @TmpTable VALUES (12.10,900);
INSERT INTO @TmpTable VALUES (12.10,1632.6);
INSERT INTO @TmpTable VALUES (12.10,1625);
INSERT INTO @TmpTable VALUES (12.10,1118.75);


SELECT ROUND(ISNULL((SELECT SUM(MaterialAmount) FROM @TmpTable), 0),2) AS MatSum,
       ISNULL((SELECT SUM(LaborAmount) FROM @TmpTable), 0) AS LabSumUnrounded, --> 7585.225
       ROUND(ISNULL((SELECT SUM(LaborAmount) FROM @TmpTable), 0),2) AS LabSum;  --> 7585.23

SELECT ROUND(SUM(MaterialAmount),2),
       ROUND(SUM(LaborAmount),2)   ---> 7585.23
   FROM @TmpTable;

Any idea/suggestion why i am getting 0.01 difference in my original scenario, while getting exact values in all my testcases ? Thanks in advance.

Upvotes: 3

Views: 16562

Answers (3)

Marcos Redondo
Marcos Redondo

Reputation: 1

Probably you are getting some sort of precission error prior to the display of the results. Try something like this:

SELECT ROUND(ROUND(ISNULL((SELECT SUM([Labor]) FROM @Intermediate ),0),3) ,2)

The inner instance of ROUND to 3 gets rid of those pesky decimals, leaving just 7585.225, the outer ROUND actualy does what it is supossed to do.

Upvotes: 0

Sandokas
Sandokas

Reputation: 325

This is because you are using float database type.

Float should not be used to represent values that require precision, since they are stored as approximations, different manipulations can give you different results.

In sql server you can use decimal and numeric data types for numerical precision: http://msdn.microsoft.com/en-us/library/ms187746.aspx

Upvotes: 5

Sonam
Sonam

Reputation: 3466

Try this way:

   SELECT ROUND(@test,2) AS Result; --> results 7585.23
   SELECT ROUND(convert(float,7585.225),2) AS Result --> results 7585.23

When you store the value as float it stores the approximate value but not exact value, when you want to store exact value use Decimal, money or small money data type. Here in your example when I converted the numeric value to float, it stored the approximate value of number.

http://msdn.microsoft.com/en-us/library/ms187912%28v=sql.105%29.aspx

Upvotes: 0

Related Questions