Dom Sinclair Dom Sinclair - 2 months ago 6
SQL Question

SQL Rounding does not appear to work as expected

I have the following SQL query.

DECLARE
@StartDate DATE ='20160101' ,
@EndDate DATE = '20160331',

@Box1 DECIMAL,
@Box2 DECIMAL,
@Box3 DECIMAL,
@Box4 DECIMAL,
@Box5 DECIMAL,
@Box6 DECIMAL,
@Box7 DECIMAL,
@Box8 DECIMAL,
@Box9 DECIMAL


SET @Box1 = (SELECT ROUND(SUM (vt.Vat),2) FROM VatTransactions vt WHERE vt.VatTransactionDate BETWEEN @StartDate AND @EndDate)

SET @Box2 = (SELECT ROUND(SUM(vt.VatDueOnECPurchases/vt.ConversionFactor),2) FROM VatTransactions vt WHERE vt.VatTransactionDate BETWEEN @StartDate AND @EndDate)

SET @Box3 = (SELECT ROUND(SUM(@Box1 + @Box2),2))

SET @Box4 = (SELECT (ROUND(SUM(vt.VatInput),2) + @Box2) FROM VatTransactions vt WHERE vt.VatTransactionDate BETWEEN @StartDate AND @EndDate)

SET @Box5 =(SELECT @Box3 - @Box4)

SET @Box8 = (SELECT ROUND(SUM(vt.SlAway/vt.ConversionFactor),2) FROM VatTransactions vt WHERE vt.VatTransactionDate BETWEEN @StartDate AND @EndDate)

SET @Box9 = (SELECT ROUND(SUM(vt.PlAway/vt.ConversionFactor),2) FROM VatTransactions vt WHERE vt.VatTransactionDate BETWEEN @StartDate AND @EndDate)

SET @Box6 = (SELECT (ROUND(SUM(vt.SlHome),2) + @Box8) FROM VatTransactions vt WHERE vt.VatTransactionDate BETWEEN @StartDate AND @EndDate)

SET @Box7 = (SELECT (ROUND(SUM(vt.PlHome),2) + @Box9) FROM VatTransactions vt WHERE vt.VatTransactionDate BETWEEN @StartDate AND @EndDate)

SELECT @Box1 AS BOX1, @Box2 AS Box2, @Box3 AS Box3, @Box4 AS Box4, @Box5 AS Box5, @Box6 AS Box6, @Box7 AS Box7, @Box8 AS Box8, @Box9 AS Box9


When this is run it produces the following Result;

enter image description here

All of the fields in the tables from which the query is getting it's information (with the obvious exception of the dates) are defined as either Money or Decimal types.

In reality the Box4 figure ought to be 1351.17 and the box5 figure should be -1351.17

Can anyone tell me why I'm seeing the results that I am, and what I ought to have been doing to get the result that I was expecting?

Thanks

Answer
@Box4  DECIMAL
       ^^^  here is your problem

You never gave your DECIMAL types any precision past the decimal point. Hence, SQL Server is displaying the values with zero digits of precision past the decimal point, i.e. as integers.

Since you seem to want two decimal places of precision, try using this declaration:

@Box4  DECIMAL(10,2)