Building retention table but need start date from a different row

Transact-SQL
1

Below is an example showing two customer accounts and their transaction information. I'm trying to calculate the number of days retained from when they started their subscription (startdate), to when they stopped their subscription (StopDate).

The trouble I have is that many times the customer will switch from a trial subscription (.99) to a full price subscription. When that happens, a new record is created for the new product but the original start date is not part of that new record.

For example, account "A2E7JBVBLTUSTHF" started a trial subscription on 6/14/13 (TransID = 100931889). Their subscription rolled into a full price subscription then subsequently stopped on 11/11/2013. For this customer, they remained active for 150 days (6/14/2013 to 11/11/2013).

Account "A2GC4RIYWEHKUHK" had a subscription start on 9/27/2014. Their subscription rolled into 2 more subscriptions and eventually ended on 7/19/2016. 661 days retained.

My question is, what is the best way to get days retained when the start date is not always associated with the record that holds the stop date. I've tried self joins, lead() and lag() functions all to no avail.

Any suggestions or help would be very much appreciated!

2

Could you provide the data in a useable format? That is, CREATE TABLE with INSERT statements and not just a picture of data. I can't test code against a picture :-).

3

Apologies. Here's the code to build the table and data.
Thanks!

CREATE TABLE temp1234(
[accountNumber] varchar,
[productprice] varchar,
[startdate] varchar,
[StopDate] varchar,
[TransID] varchar
)

insert into temp1234 values ('A2GC4RIYWEHKUHK','0.99','09/27/2014','09/27/2014','102074252')
insert into temp1234 values ('A2GC4RIYWEHKUHK','6.95','','07/06/2013','100554048')
insert into temp1234 values ('A2GC4RIYWEHKUHK','0.99','12/10/2012','12/10/2012','100468876')
insert into temp1234 values ('A2GC4RIYWEHKUHK','7.95','','05/20/2016','102141704')
insert into temp1234 values ('A2E7JBVBLTUSTHF','6.95','','11/11/2013','101009068')
insert into temp1234 values ('A2E7JBVBLTUSTHF','0.99','06/14/2013','06/14/2013','100931889')
insert into temp1234 values ('A2E7JBVBLTUSTHF','9.95','','01/04/2016','103022889')
insert into temp1234 values ('A2E7JBVBLTUSTHF','0.99','10/06/2015','10/06/2015','102871203')
insert into temp1234 values ('A2E7JBVBLTUSTHF','9.95','07/11/2014','04/07/2015','101884569')
insert into temp1234 values ('A2GC4RIYWEHKUHK','12.99','','07/19/2016','103317622')

select *
from temp1234
order by accountnumber,transID

4

This might be easier with LEAD and/or LAG, but I didn't want to assume those would be available. Instead, I used a temp table so that it could be indexed for efficiency.

IF OBJECT_ID('tempdb.dbo.#temp1234') IS NOT NULL
    DROP TABLE #temp1234
SELECT TOP (0) IDENTITY(int, 1, 1) AS ident, CAST(0 AS int) AS row_num, *
INTO #temp1234
FROM dbo.temp1234

CREATE CLUSTERED INDEX temp1234__CL ON #temp1234 ( ident ) WITH ( FILLFACTOR = 100 );

INSERT INTO #temp1234
SELECT ROW_NUMBER() OVER(PARTITION BY accountNumber ORDER BY StopDate) AS row_num, *
FROM dbo.temp1234

/*add a "dummy" row so the last accountNumber entry finds a StopDate*/
INSERT INTO #temp1234 ( accountNumber, startdate )
SELECT 'ZZZZ', '20790101'


SELECT t.accountNumber, t.startdate, get_stop_date.StopDate, 
    DATEDIFF(DAY, t.startdate, get_stop_date.StopDate) AS active_days    
FROM #temp1234 t
OUTER APPLY (
    SELECT TOP (1) ident
    FROM #temp1234 t_next
    WHERE t_next.ident > t.ident AND
        t_next.startdate > ''
    ORDER BY t_next.ident
) AS find_next_start_date
OUTER APPLY (
    SELECT CASE
        WHEN find_next_start_date.ident = t.ident + 1  THEN t.StopDate
        ELSE (SELECT t_stop.StopDate FROM #temp1234 t_stop 
              WHERE t_stop.ident = find_next_start_date.ident - 1)
        END AS StopDate
) AS get_stop_date
WHERE t.startdate > '' AND t.ident < (SELECT MAX(ident) FROM #temp1234)
1 Like
5

Scott - this is AWESOME! Exactly what I needed!!!

If I could, I would buy you a beer! haha

Thanks for your help! Very much appreciated!

Chad