CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-procedures
SBB
SBB

Reputation: 8970

TSQL Divide by Zero Error

I have a query that is doing some math and in there are times where a number could be a zero that its dividing by which would cause an error.

I found something on this website about how to fix it but now the number doesnt change at all.

Example Data: 
los.shortTermLosses = 1
A.shortTerm = 15
Giving the equation of 1/15*12 = 0.8

COALESCE(los.shortTermLosses / NULLIF(A.shortTerm,0),0.00)* 12 AS shortTermAttrition

It has to be something I did to prevent the divide by zero error but not sure how to get it to work correctly. The current result is always the 0.00

Update

For those who want to see the whole query..

SELECT A.QID,
          (SELECT    TOP 1 E.[FirstName],
                           E.[LastName],
                           E.[NTID],
                           E.[TitleDesc],
                           A.[countOfDirects],
                           A.[longTerm],
                           A.[shortTerm],
                           COALESCE(los.totalLosses,0) totalLosses,
                           COALESCE(los.longTermLosses, 0) longTermLosses,
                           COALESCE(los.shortTermLosses,0) shortTermLosses,
                           COALESCE(los.shortTermLosses / NULLIF(A.shortTerm,0),0.00)* 12 AS shortTermAttrition,
                           COALESCE(los.longTermLosses / NULLIF(A.longTerm,0),0.00)* 12 AS longTermAttrition,
                           COALESCE(los.totalLosses / NULLIF(A.countOfDirects,0),0.00)* 12 AS totalAttrition
            FROM     employeeTable_historical AS E
            OUTER APPLY (SELECT         COUNT(b.leaver) as [totalLosses],
                                        sum(case when b.term = 'LTA' then 1 else 0 end) as [longTermLosses],
                                        sum(case when b.term = 'STA' then 1 else 0 end) as [shortTermLosses]
                                FROM dbo.attritionData AS B
                                WHERE E.QID = B.supervisor
                                AND MONTH(B.leaveDate) = @month
                                AND YEAR(B.leaveDate) = @year
                                GROUP BY b.supervisor
            )los
            WHERE    E.qid = A.[QID]
                     AND CONVERT (DATE, dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1)) >= CONVERT (DATE, E.[Meta_LogDate])
            ORDER BY meta_logDate DESC
            FOR      XML PATH (''), TYPE, ELEMENTS)
    FROM   (SELECT QID,
                   [timestamp],
                   [countOfDirects],
                   [longTerm],
                   [shortTerm]
            FROM   (SELECT QID,
                           [timestamp],
                           [countOfDirects],
                           [shortTerm],
                           [longTerm],
                           ROW_NUMBER() OVER (PARTITION BY QID ORDER BY [Timestamp]) AS Row
                    FROM   [red].[dbo].[attritionCounts]
                    WHERE  [mgrQID] = @director
                           AND YEAR(CAST ([timestamp] AS DATE)) = @year
                           AND MONTH(CAST ([timestamp] AS DATE)) = @month) AS Tmp1
            WHERE  Row = 1) AS A
    FOR    XML PATH ('DirectReport'), TYPE, ELEMENTS, ROOT ('Root');

Upvotes: 0

Views: 163

Answers (1)

bummi
bummi

Reputation: 27377

You error does not come from the NULLIF, you are just dividing an Integer e.g. 1 / 15 = 0
, just change your term to:
COALESCE(CAST(los.shortTermLosses as float) / NULLIF(A.shortTerm,0),0.00)* 12 AS shortTermAttrition

The closest transformation for you XML export might be casting the Result as money

Declare  @shortTermLosses int = 1
Declare  @shortTerm int = 15

select 
CAST(
COALESCE(CAST(@shortTermLosses AS float) / Cast(NULLIF(@shortTerm,0) AS float),0.00)* 12 
as Money)
AS shortTermAttrition
FOR      XML PATH (''), TYPE, ELEMENTS

Upvotes: 1

Related Questions