calculating 2 dates within the same column based on condition

Question

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.

Accepted Answer

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

*/

Answer

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

Share via

calculating 2 dates within the same column based on condition

1 additional answer