locked
division to return null when null else 0 if division occurs by 0 RRS feed

  • Question

  • Hi,

    I want to achieve the Value column to return below:

    -return null when either divisor or dividend is null

    -return 0 if division by 0

    -else return dividend/divisor

    select 'LostShipments' as KPI,case count([ItemNum]) when null then null when 0 then 0 else cast(count(case when [Lost]>1 then [ItemNum] else null end) as float)/cast(count([ItemNum]) as float) end as 'value' from TBL b where [Customer] like 'abb in%' and YrMonth between format(DATEADD(month,-12, convert(date,'2020-04'+'-01')), 'yyyy-MM') and '2020-04' 

    The above is not doing what is expected properly,so pls guide.

    Monday, May 4, 2020 2:05 PM

Answers



  • create table #sample(Customer varchar(10),Kpi varchar(50),[YrMonth] varchar(10),ItemNum varchar(100),Lost numeric)
    insert #sample values('ABC','LostShipments','2020-04','3.1',0),
                           ('ABC','LostShipments','2020-02','3.2',1),
    ('ABC','LostShipments','2020-01','2.1',0),
    ('ABC','LostShipments','2019-12','4.1',0),
    ('ABC','LostShipments','2020-03','1.1',1),
    ('ABC','LostShipments','2019-11','3.1',0)
    
    SELECT KPI
    ,CASE count([ItemNum]) 
    WHEN 0 THEN 0
    ELSE nullif(cast(count(CASE WHEN [Lost] > 1 THEN [ItemNum] ELSE NULL END) AS FLOAT) ,0)
    / nullif(cast(count([ItemNum]) AS FLOAT),0)
    END AS 'value'
    FROM #sample b
    WHERE [Customer] LIKE 'abc%'
    AND YrMonth BETWEEN format(DATEADD(month, - 12, convert(DATE, '2020-04' + '-01')), 'yyyy-MM')
    AND '2020-04'
    GROUP BY kpi
    /*
    KPI	value
    LostShipments	NULL
    */
    
    drop   table #sample 


    • Proposed as answer by pituachMVP Monday, May 4, 2020 4:42 PM
    • Marked as answer by msdnpublic1234 Monday, May 4, 2020 8:28 PM
    Monday, May 4, 2020 4:21 PM

All replies

  • Please post your question with your sample DDL and data inserts script.

    Monday, May 4, 2020 2:39 PM
  • create table #TBL(Customer varchar(10),Kpi varchar(50),[YrMonth] varchar(10),ItemNum varchar(100),Lost numeric)
    insert #TBL values('ABC','LostShipments','2020-04','3.1',0),
                           ('ABC','LostShipments','2020-02','3.2',1),
    ('ABC','LostShipments','2020-01','2.1',0),
    ('ABC','LostShipments','2019-12','4.1',0),
    ('ABC','LostShipments','2020-03','1.1',1),
    ('ABC','LostShipments','2019-11','3.1',0)
    Monday, May 4, 2020 3:25 PM
  • Do you know count([ItemNum])  will not return null?

    What do you want from your sample data?

    Monday, May 4, 2020 3:32 PM
  • I want to achieve the Value based on following conditions:

    -return null when either divisor or dividend is null

    -return 0 if division by 0

    -else return dividend/divisor

    This query of mine is returning 0 and ignoring null as a result .Either of numerator or denominator may be null.

    SELECT KPI
    ,CASE count([ItemNum])
    WHEN NULL
    THEN NULL
    WHEN 0
    THEN 0
    ELSE cast(count(CASE 
    WHEN [Lost] > 1
    THEN [ItemNum]
    ELSE NULL
    END) AS FLOAT) / cast(count([ItemNum]) AS FLOAT)
    END AS 'value'
    FROM #sample b
    WHERE [Customer] LIKE 'abc%'
    AND YrMonth BETWEEN format(DATEADD(month, - 12, convert(DATE, '2020-04' + '-01')), 'yyyy-MM')
    AND '2020-04'
    GROUP BY kpi

    Monday, May 4, 2020 3:41 PM
  • What are you expecting from this small sample dataset?
    Monday, May 4, 2020 3:57 PM


  • Monday, May 4, 2020 4:01 PM


  • create table #sample(Customer varchar(10),Kpi varchar(50),[YrMonth] varchar(10),ItemNum varchar(100),Lost numeric)
    insert #sample values('ABC','LostShipments','2020-04','3.1',0),
                           ('ABC','LostShipments','2020-02','3.2',1),
    ('ABC','LostShipments','2020-01','2.1',0),
    ('ABC','LostShipments','2019-12','4.1',0),
    ('ABC','LostShipments','2020-03','1.1',1),
    ('ABC','LostShipments','2019-11','3.1',0)
    
    SELECT KPI
    ,CASE count([ItemNum]) 
    WHEN 0 THEN 0
    ELSE nullif(cast(count(CASE WHEN [Lost] > 1 THEN [ItemNum] ELSE NULL END) AS FLOAT) ,0)
    / nullif(cast(count([ItemNum]) AS FLOAT),0)
    END AS 'value'
    FROM #sample b
    WHERE [Customer] LIKE 'abc%'
    AND YrMonth BETWEEN format(DATEADD(month, - 12, convert(DATE, '2020-04' + '-01')), 'yyyy-MM')
    AND '2020-04'
    GROUP BY kpi
    /*
    KPI	value
    LostShipments	NULL
    */
    
    drop   table #sample 


    • Proposed as answer by pituachMVP Monday, May 4, 2020 4:42 PM
    • Marked as answer by msdnpublic1234 Monday, May 4, 2020 8:28 PM
    Monday, May 4, 2020 4:21 PM
  • Thanks a lot Jingyang
    Monday, May 4, 2020 8:28 PM