sqlt-sqlcastingzeronullif

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


Solution

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