Flag the date when they return

Story:

For each id , they have a join date to a subscription and when they get rebilled monthly, they have a returning date. The first part of the exercise was to flag consecutive months of returned dates from the join date. Here’s an example:

+----+------------+----------------+------+
| id | join_date  | returning_date | flag |
+----+------------+----------------+------+
|  1 | 2018-12-01 | 2019-01-01     |    1 |
|  1 | 2018-12-01 | 2019-02-01     |    1 |
|  1 | 2018-12-01 | 2019-03-01     |    1 |
+----+------------+----------------+------+

Objective:

What I would like to add is to flag those who return from a canceled subscription. That flag can be in another column. For example the following results shows that on May 1st 2019 , he returned. This date needs to be flagged:

+----+------------+----------------+------+
| id | join_date  | returning_date | flag |
+----+------------+----------------+------+
|  1 | 2018-12-01 | 2019-01-01     |    1 |
|  1 | 2018-12-01 | 2019-02-01     |    1 |
|  1 | 2018-12-01 | 2019-03-01     |    1 |
|  1 | 2018-12-01 | 2019-05-01     |    0 |
|  1 | 2018-12-01 | 2019-06-01     |    0 |
+----+------------+----------------+------+

Fiddle Data:

DROP TABLE IF EXISTS #T1
create table #t1 (id int,join_date date, returning_date date)
insert into #t1 values
(1,'2018-12-01', '2019-01-01'),
(1,'2018-12-01', '2019-02-01'),
(1,'2018-12-01', '2019-03-01'),
(1,'2018-12-01', '2019-05-01'),
(1,'2018-12-01', '2019-06-01'),
(2,'2018-12-01', '2019-02-01'),
(2,'2018-12-01', '2019-03-01'),
(2,'2018-12-01', '2019-05-01'),
(2,'2018-12-01', '2019-06-01'),
(3,'2019-05-01', '2019-06-01'),
(3,'2019-05-01', '2019-08-01'),
(3,'2019-05-01', '2019-10-01')

Current query with flag for consecutive months:

select *
,CASE WHEN DATEDIFF(MONTH,join_date,returning_date) = ROW_NUMBER() OVER (PARTITION BY id ORDER BY returning_date ASC) THEN 1 ELSE 0 END AS flag
from #t1
ORDER BY ID,returning_date

Answer

since you didn’t specify which recurrence of returning as the target to flag, my query flags any non-consecutive date as a return date cause a subscriber could leave and return many times after their join date (the subscriber with [id] 3 technically returned in August and then again in October so that’s returning twice but October is marked as LAST instead based on the data set). i also made it easier to read by adding in start date and end date based on the data set in your fiddle.

you can use this query as a temp table, cte, basis, or whatever to continue to query against if you need to manipulate the data further.

select a.*
    ,case
        when a.returning_date = (select min(c.returning_date) from subscription c where c.id = a.id and c.join_date = a.join_date) then 'START'
        when a.returning_date = (select max(c.returning_date) from subscription c where c.id = a.id and c.join_date = a.join_date) then 'END'
        when b.id is null then 'RETURN'
        else 'CONSECUTIVE'
    end as SubStatus
from subscription a
        left join subscription b on a.id = b.id and a.join_date = b.join_date and DATEADD(month,-1,a.returning_date) = b.returning_date

here is the result set from my query:

id          join_date  returning_date SubStatus
----------- ---------- -------------- -----------
1           2018-12-01 2019-01-01     START
1           2018-12-01 2019-02-01     CONSECUTIVE
1           2018-12-01 2019-03-01     CONSECUTIVE
1           2018-12-01 2019-05-01     RETURN
1           2018-12-01 2019-06-01     END
2           2018-12-01 2019-02-01     START
2           2018-12-01 2019-03-01     CONSECUTIVE
2           2018-12-01 2019-05-01     RETURN
2           2018-12-01 2019-06-01     END
3           2019-05-01 2019-06-01     START
3           2019-05-01 2019-08-01     RETURN
3           2019-05-01 2019-10-01     END