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;

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

Why am I seeing the results above, and what I ought to do to get the result that I am expecting?

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)

Advertisement

Answer