calculating 2 dates within the same column based on condition

learning_step_by_step 61 Reputation points
2020-10-22T18:53:05.843+00:00

Hi All,

I have the following table call chek_Status: This table tracked daily the client and shows them if they are on yearly, monthly or weekly subscription plan. It also shows if they are new new business (when they sign up) to when they cancel or the day they re-activativate until cancel

day_of external_subscription_id plan_name sstatus_type
4/23/2019 0:00 304585LP yearly New Business
4/24/2019 0:00 304585LP yearly Regular
4/25/2019 0:00 304585LP yearly Regular
10/20/2020 0:00 304585LP yearly cancelled
1/4/2017 0:00 OPK6518 monthly Reactivation
1/24/2020 0:00 OPK6518 monthly regular
1/25/2020 0:00 OPK6518 monthly regular
6/22/2020 0:00 OPK6518 monthly cancelled
3/1/2018 0:00 4598KCP weekly New Business
3/2/2018 0:00 4598KCP weekly regular
3/3/2018 0:00 4598KCP weekly regular
3/4/2018 0:00 4598KCP weekly regular
12/5/2018 0:00 4598KCP weekly cancelled
7/15/2020 0:00 458CPLK quarterly Reactivation
7/16/2020 0:00 458CPLK quarterly Reactivation
7/17/2020 0:00 458CPLK quarterly Reactivation
7/18/2020 0:00 458CPLK quarterly Reactivation
7/19/2020 0:00 458CPLK quarterly Reactivation
8/20/2020 0:00 458CPLK quarterly cancelled
3/4/2020 0:00 ZIPC25UP monthly Reactivation
3/5/2020 0:00 ZIPC25UP monthly regular
3/6/2020 0:00 ZIPC25UP monthly Regular
6/22/2020 0:00 ZIPC25UP monthly cancelled

Ideally, I want to find out how long the person/business was a member before they cancel. new business cancelation by plan/ reactivation cancelation by plan

The table would look like this
day_of plan_name status_type day range
10/20/2020 yearly New Business 546
6/22/2020 monthly Reactivation 1375
12/5/2018 weekly New Business 279
8/20/2020 quarterly Reactivation 36

Thanks you for the help as I''m a little struggling to figure out a good logic for this since the start and end date are both in the same column.

Azure Database for PostgreSQL
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 110K Reputation points
    2020-10-22T20:00:45.87+00:00

    Check this example too:

    declare @table as table (day_of date, external_subscription_id varchar(20), plan_name varchar(20), status_type varchar(20))
    
    insert @table values
    ( '04/23/2019 0:00', '304585LP', 'yearly', 'New Business   ' ),
    ( '04/24/2019 0:00', '304585LP', 'yearly', 'Regular        ' ),
    ( '04/25/2019 0:00', '304585LP', 'yearly', 'Regular        ' ),
    ( '10/20/2020 0:00', '304585LP', 'yearly', 'cancelled      ' ),
    ( '01/04/2017 0:00', 'OPK6518 ', 'monthly', 'Reactivation  ' ),
    ( '01/24/2020 0:00', 'OPK6518 ', 'monthly', 'regular       ' ),
    ( '01/25/2020 0:00', 'OPK6518 ', 'monthly', 'regular       ' ),
    ( '06/22/2020 0:00', 'OPK6518 ', 'monthly', 'cancelled     ' ),
    ( '03/01/2018 0:00', '4598KCP ', 'weekly', 'New Business   ' ),
    ( '03/02/2018 0:00', '4598KCP ', 'weekly', 'regular        ' ),
    ( '03/03/2018 0:00', '4598KCP ', 'weekly', 'regular        ' ),
    ( '03/04/2018 0:00', '4598KCP ', 'weekly', 'regular        ' ),
    ( '12/05/2018 0:00', '4598KCP ', 'weekly', 'cancelled      ' ),
    ( '07/15/2020 0:00', '458CPLK ', 'quarterly', 'Reactivation' ),
    ( '07/16/2020 0:00', '458CPLK ', 'quarterly', 'Reactivation' ),
    ( '07/17/2020 0:00', '458CPLK ', 'quarterly', 'Reactivation' ),
    ( '07/18/2020 0:00', '458CPLK ', 'quarterly', 'Reactivation' ),
    ( '07/19/2020 0:00', '458CPLK ', 'quarterly', 'Reactivation' ),
    ( '08/20/2020 0:00', '458CPLK ', 'quarterly', 'cancelled   ' ),
    ( '03/04/2020 0:00', 'ZIPC25UP', 'monthly', 'Reactivation  ' ),
    ( '03/05/2020 0:00', 'ZIPC25UP', 'monthly', 'regular       ' ),
    ( '03/06/2020 0:00', 'ZIPC25UP', 'monthly', 'Regular       ' ),
    ( '06/22/2020 0:00', 'ZIPC25UP', 'monthly', 'cancelled     ' )
    
    select * from  @table
    
    ---
    
    ;
    with 
    Q1 as
    (
        select *, ROW_NUMBER() over (partition by external_subscription_id, plan_name order by day_of) as rn
        from @table
        where status_type in ('New Business', 'Reactivation', 'cancelled')
    ), 
    Q2 as
    (
        select *, DATEDIFF(d, day_of, LEAD(day_of) over (partition by external_subscription_id, plan_name order by rn)) as d 
        from Q1 
        where rn = 1 or status_type = 'cancelled'
    ), 
    Q3 as
    (
        select plan_name, status_type, sum(d) as [day range]
        from Q2
        where status_type in ('New Business', 'Reactivation')
        group by plan_name, status_type
    )
    select
        (select max(day_of) from @table as t where t.plan_name = Q3.plan_name) as day_of,
        *
    from Q3
    order by day_of
    
    /*
    
    day_of     plan_name            status_type          day range
    ---------- -------------------- -------------------- -----------
    2018-12-05 weekly               New Business         279
    2020-06-22 monthly              Reactivation         1375
    2020-08-20 quarterly            Reactivation         36
    2020-10-20 yearly               New Business         546
    
    */
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Ian Bates 91 Reputation points
    2020-10-22T19:38:41.817+00:00

    Hi @learning_step_by_step , Here is my attempt at your query.

    CREATE TABLE #Table (  
    	day_of DATETIME  
    	,external_subscription_id VARCHAR(10)  
    	,plan_name VARCHAR(15)  
    	,status_type VARCHAR(15))  
      
    INSERT INTO #Table VALUES  
    	('4/23/2019 0:00','304585LP','yearly','New Business')  
    	,('4/24/2019 0:00','304585LP','yearly','Regular')  
    	,('4/25/2019 0:00','304585LP','yearly','Regular')  
    	,('10/20/2020 0:00','304585LP','yearly','cancelled')  
    	,('1/4/2017 0:00','OPK6518','monthly','Reactivation')  
    	,('1/24/2020 0:00','OPK6518','monthly','regular')  
    	,('1/25/2020 0:00','OPK6518','monthly','regular')  
    	,('6/22/2020 0:00','OPK6518','monthly','cancelled')  
      
    SELECT T1.external_subscription_id+' '+CONVERT(VARCHAR(10),T2.day_of,101)+' '+T2.status_type+' '+CAST(DATEDIFF(D,T1.day_of,T2.day_of) AS VARCHAR(6)) AS FinalValue  
    FROM (SELECT external_subscription_id  
    			,MIN(day_of) AS day_of  
    		FROM #Table  
    		GROUP BY external_subscription_id) T1  
    	LEFT JOIN #Table T2  
    		ON T1.external_subscription_id=T2.external_subscription_id  
    		AND T1.day_of<T2.day_of  
    

    34418-image.png

    1 person found this answer helpful.
    0 comments No comments