Divide by Case When

Question

I'm currently doing some work on a 6 month rolling report, but i seem to be a little bit stuck on one area.

I have one column Liveatcutoff_PC it will be populated by 1 for live or 0 for not live. If it's 0 it means it's cancelled. I want to work out the cancellation % for the date range in my code but I'm not having much luck.

SELECT 

UserID
,Agent
,COUNT(CASE WHEN SaleDate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-6, 0)
    AND SaleDate < DATEADD(MONTH, DATEDIFF(MONTH, -6, GETDATE())-6, 0)
    AND LiveAtCutOff_PC='0' THEN LiveAtCutOff_PC END)

FROM PDS_SALES_PMI

WHERE SaleDate>='2019-01-01'
AND Leaver='0'
GROUP BY UserID, Agent

Help much appreciated.

So you want to find out what is the percentage of canceled sales in your table for the 6 month period? — Kelevra, Oct 01 '19 at 14:53
Just for the month 6 months ago, but that month will move due to the date range. — Wyseguy, Oct 01 '19 at 14:57
And you want to group by AgentID and UserID. so you need percentage of canceled sales per agentID per userID? — Kelevra, Oct 01 '19 at 15:00
What is all that -6 date math supposed to be doing for the end of the range? — shawnt00, Oct 01 '19 at 18:02
@Shawnt00 it references the last day of the month in the start date. — Wyseguy, Oct 03 '19 at 08:45
Yes, it is strictly less than. Wouldn't it make more sense to use the same expression as the first but with -5? — shawnt00, Oct 03 '19 at 14:16

Gordon Linoff · Answer 1 · 2019-10-01T15:22:41.923

1

If I understand correctly, you can use AVG():

AVG(CASE WHEN SaleDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-6, 0) AND
              SaleDate < DATEADD(MONTH, DATEDIFF(MONTH, -6, GETDATE())-6, 0)       THEN LiveAtCutOff_PC * 1.0
    END) as LiveAtCutoff_PC_ratio

Your question specifies that LiveAtCutOff_PC has values of 0 and 1, which implies that the field is a number. However, the code compares it to a string, which suggests otherwise. If the values are only 0 and 1 then the above should work for both numbers and strings (although for bit you would need to convert the value).

edited Oct 01 '19 at 15:22

answered Oct 01 '19 at 15:05

Gordon Linoff

1,242,037
58
646
786

Unless those numbers are integers, in which case it won't work, e.g. `WITH x AS (SELECT 0 AS x UNION ALL SELECT 1) SELECT AVG(x) FROM x;` returns 0, because 0 and 1 are both integers. To get a decimal answer you would need something like `WITH x AS (SELECT 0.0 AS x UNION ALL SELECT 1) SELECT AVG(x) FROM x;` – Richard Hansell Oct 01 '19 at 15:21
@RichardHansell . . . I adjusted the answer to take that into account. – Gordon Linoff Oct 01 '19 at 15:22
Cool, I missed the `* 1.0` as it was off the side of the query! – Richard Hansell Oct 01 '19 at 15:24
@gordonLinoff, Thank you for the reply - I think this goes some way towards solving this. However, The cancellation % does need to go against total policies solid and I'm not sure this calculates this correctly based on the results. – Wyseguy Oct 03 '19 at 08:19

Kelevra · Answer 2 · 2019-10-01T18:30:28.650

So since you requirement is to find out the percentage of canceled sales then you don't have to do it in one query. Case will just calculate everything, including nulls(that will come up because you don't have 'else') you might want to try subquery instead or use CTE.

CTE example

with CTE as (
Select  UserID
       ,Agent
       ,COUNT(*) as Canceled
from PDS_SALES_PMI
where   SaleDate >=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-6, 0)
    AND SaleDate < DATEADD(MONTH, DATEDIFF(MONTH, -6, GETDATE())-6, 0)
    AND LiveAtCutOff_PC='0'
    AND SaleDate >='2019-01-01'
    AND Leaver = 0
group by UserID, AgentID)

Select a.UserID
      ,a.AgentID
      ,Canceled/COUNT(*) * 100.0 as Canceled_Percent
From PDS_SALES_PMI a INNER JOIN CTE 
on  a.AgentID = CTE.AgentID
and a.UserID  = CTE.UserID
Where SaleDate >=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-6, 0)
  AND SaleDate < DATEADD(MONTH, DATEDIFF(MONTH, -6, GETDATE())-6, 0)
  AND SaleDate >='2019-01-01'
  AND Leaver = 0
Group by a.UserID, a.AgentID

Subquery Example

Select a.UserID
          ,a.AgentID
          ,Canceled/COUNT(*) * 100.0 as Canceled_Percent
    From PDS_SALES_PMI a INNER JOIN (
                   Select  UserID
                          ,Agent
                          ,COUNT(*) as Canceled
                   from PDS_SALES_PMI
                   where SaleDate >=DATEADD(MONTH, DATEDIFF(MONTH, 0,GETDATE())-6, 0)
                     AND SaleDate < DATEADD(MONTH, DATEDIFF(MONTH, -6, GETDATE())-6, 0)
                     AND LiveAtCutOff_PC='0'
                     AND SaleDate >='2019-01-01'
                     AND Leaver = 0
                   group by UserID, AgentID) as Tmp
    on  a.AgentID = Tmp.AgentID
    and a.UserID  = Tmp.UserID
    Where SaleDate >=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-6, 0)
      AND SaleDate < DATEADD(MONTH, DATEDIFF(MONTH, -6, GETDATE())-6, 0)
      AND SaleDate >='2019-01-01'
      AND Leaver = 0
    Group by a.UserID, a.AgentID

...and this has the same problem with integer division. It's easy to fix, just change `100` to `100.0` for example. — Richard Hansell, Oct 01 '19 at 15:23
@RichardHansell I don't see the problem with *100. If you get 2/10= 0.2. 0.2 *100 = 20 — Kelevra, Oct 01 '19 at 15:31
integer math. 2/10 will not equal 0.2 https://stackoverflow.com/questions/3443672/integer-division-in-sql-server — avery_larry, Oct 01 '19 at 16:27
@RichardHansell Thank you, didn't know about that, added to the answer — Kelevra, Oct 01 '19 at 18:30

Divide by Case When

2 Answers2