CODEWITHSUNDEEP
t-sqlsql-server-2016
MISNole
MISNole

Reputation: 1062

Divide by 0 Error Inside Windows Function

I am trying to calculate the % of closed claims divided by total claims per month using Windows functions. But some months will have zero total claims which results in the Divide by zero error encountered message.

I have tried writing a CASE statement to handle where the total claims equal zero to be set the percentage to zero but I have not had any luck so far.

Below is a screenshot of what I am expecting:

enter image description here

And the TSQL below are my attempts at error handling the divide by zero message: I've added a NULLIF on the bottom of the divider and an attempt at a CASE statement to set the value = 0 when the bottom divider is zero - both result in the same error. Any suggestions on how to get around this error?

CREATE TABLE #ClaimCounts
(
Year INT,
ClaimStatus VARCHAR (50),
LossMonth DATE,
ClaimMonth DATE,
ClaimCount INT
);

INSERT INTO #ClaimCounts
(
Year,
ClaimStatus,
LossMonth,
ClaimMonth,
ClaimCount
)
VALUES
(2008, 'Closed', '20080630', '20080131', 0),
(2008, 'Total', '20080630', '20080131', 0),
(2008, 'Closed', '20080630', '20080229', 0),
(2008, 'Total', '20080630', '20080229', 0),
(2008, 'Closed', '20080630', '20080331', 0),
(2008, 'Total', '20080630', '20080331', 0),
(2008, 'Closed', '20080630', '20080430', 0),
(2008, 'Total', '20080630', '20080430', 0),
(2008, 'Closed', '20080630', '20080531', 0),
(2008, 'Total', '20080630', '20080531', 0),
(2008, 'Closed', '20080630', '20080630', 0),
(2008, 'Total', '20080630', '20080630', 6),
(2008, 'Closed', '20080630', '20090731', 2),
(2008, 'Total', '20080630', '20090731', 5),
(2008, 'Closed', '20080630', '20080831', 1),
(2008, 'Total', '20080630', '20080831', 1),
(2008, 'Closed', '200806308', '20080930', 3),
(2008, 'Total', '20080630', '20080930', 3),
(2008, 'Closed', '20080630', '20081031', 2),
(2008, 'Total', '20080630', '20081031', 3),
(2008, 'Closed', '200806308', '20081130', 0),
(2008, 'Total', '20080630', '20081130', 0);

SELECT Year,
   ClaimStatus,
   LossMonth,
   ClaimMonth,
   ClaimCount,
   SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) / 
   SUM ( CASE WHEN ClaimStatus = 'Total' THEN ClaimCount * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) AS PercentageClosedClaims1 ,

   SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) / 
   SUM ( CASE WHEN ClaimStatus = 'Total' THEN NULLIF(ClaimCount, 0) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) AS PercentageClosedClaims2 ,

   CASE WHEN ClaimStatus = 'Total' AND ClaimCount = 0 THEN 0
       ELSE SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) / 
            SUM ( CASE WHEN ClaimStatus = 'Total' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth)
   END AS PercentageClosedClaims3
FROM #ClaimCounts;

DROP TABLE IF EXISTS #ClaimCounts;

Upvotes: 3

Views: 637

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

Here is a working option. The IsNull() is optional. I personally don't mind NULL values.

SELECT Year
      ,ClaimStatus
      ,LossMonth
      ,ClaimMonth
      ,ClaimCount
      ,PercentageClosedClaims1 = IsNull(SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) 
                                        / nullif(SUM ( CASE WHEN ClaimStatus = 'Total' THEN ClaimCount * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth),0) 
                                        ,0)
      ,PercentageClosedClaims2 = IsNull(SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) 
                                        / nullif(SUM ( CASE WHEN ClaimStatus = 'Total' THEN NULLIF(ClaimCount, 0) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth),0) 
                                        ,0)
      ,PercentageClosedClaims3 = IsNull(SUM ( CASE WHEN ClaimStatus = 'Closed' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth) 
                                        / nullif(SUM ( CASE WHEN ClaimStatus = 'Total' THEN ISNULL ( ClaimCount, 0 ) * 1.0 ELSE 0 END ) OVER (PARTITION BY Year, ClaimMonth),0)
                                        ,0)

FROM #ClaimCounts;

Upvotes: 2

Related Questions