Oleg Oleg - 3 months ago 19
SQL Question

Division gives values 0

In order to avoid division by 0 I am using CAST AS FLOAT and NULLIF.
But for some reason it gives me values as 0.
All I am doing is dividing

Declined
on
Submissions

What am I doing wrong?

SELECT [Status Reason],
b.MonthNum,
b.YearNum,
ISNULL(SUM(CASE WHEN Status = 'Declined' THEN 1 ELSE 0 END ),0) as Declined,
(
SELECT COUNT(ControlNo)
FROM ClearanceReportMetrics
WHERE YEAR(EffectiveDate) =YEAR(GETDATE()) AND CompanyLine = 'Ironshore Insurance Company'
AND LineOfBusiness NOT IN ('SSP Commercial General Liability','SSP Property') AND Underwriter <> 'Batcheller, Jerry'
) AS Submissions,

/*And this is what gives 0's */

COALESCE(CAST(SUM(CASE WHEN Status = 'Declined' THEN 1 ELSE 0 END ) AS FLOAT) /
( /* Here I am using NULLIF to avoid division by 0 */
SELECT NULLIF(COUNT(ControlNo),0)
FROM ClearanceReportMetrics
WHERE EffectiveDate =YEAR(GETDATE()) AND CompanyLine = 'Ironshore Insurance Company'
AND LineOfBusiness NOT IN ('SSP Commercial General Liability','SSP Property') AND Underwriter <> 'Batcheller, Jerry'
),0) AS DeclinedRatio

FROM tblCalendar b
LEFT JOIN ClearanceReportMetrics a ON b.MonthNum = MONTH(a.EffectiveDate) and b.YearNum = YEAR(a.EffectiveDate)
--AND EffectiveDate >=DateAdd(yy, -1, DATEADD(d, 1, EOMONTH(GETDATE()))) AND EffectiveDate <= EOMONTH(GETDATE()) AND CompanyLine = 'Ironshore Insurance Company' AND Status = 'Declined'
--AND LineOfBusiness NOT IN ('SSP Commercial General Liability','SSP Property')
WHERE b.YearNum = YEAR(GETDATE())
GROUP BY b.YearNum,b.MonthNum,[Status Reason]
ORDER BY b.YearNum, b.MonthNum ,Declined DESC


enter image description here

smj smj
Answer

here is one way to solve it, and to make the query easier to read (removes the nasty duplication of the code that calculates declines and submissions).

Example using dummy inner data. Move the ratio calculation to an outer query.

SELECT
   x.*
  ,CASE
       WHEN Submissions > 0
       THEN CAST(Declined AS FLOAT) / Submissions
       ELSE NULL
   END DeclinedRatio
FROM
(
    -- Dummy data, replace with existing query (except ratio calculation).
    SELECT
           'Does Not Meet Underwriting Guidelines' [Status Reason]
          ,1 MonthNum
          ,2016 YearNum
          ,5 Declined
          ,10 Submissions
) x;

Assumption, declines will never exceed submissions.

I think it may be better to return NULL in the "divide by 0" case rather than 0, as 0 is a genuine result.

Comments