Search code examples
sql-serverlightswitch-2013

Stop SUM rounding SQL

I am creating a SQL view using this SELECT statement:

SELECT 
ISNULL(MONTH(DtRepairComplete), 0) AS RepairMonthNo
,ISNULL(FORMAT(DtRepairComplete, 'MMMM'), GETDATE()) AS RepairMonth
,ISNULL(YEAR(DtRepairComplete), 0) AS RepairYear
,ISNULL(SUM(IntRepairCosts), 0) AS TotalRepairCosts
,COUNT(IntIncidentID) AS RepairCount

FROM Incident d

GROUP BY FORMAT(DtRepairComplete, 'MMMM'),


YEAR(DtRepairComplete)
,MONTH(DtRepairComplete)
,FORMAT(DtRepairComplete, 'MMMM')

IntRepairCosts is data type decimal(18,0) on the table. When I attach the view to my project, it is rounding to whole numbers. As this value represents a cost, I need it to be accurate and not round up or down.

Can I stop this field from rounding and still SUM per the query?

Thank you in advance.

Solution

  • A data type of Decimal(18,0) will have no digits after the decimal and will round up to the nearest whole value. This is why you are experiencing this. You can test this as follows:

    Declare @Value decimal(18,0) = 123.678
Select @Value

    You will get 124

    Off hand I don't know why someone would create a type of Decimal(N,0) instead of just an int or bigint. And when you're working with money, a Decimal(N,0) doesn't make any sense.