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
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.