Flag records 7 days from the previously identify record

Question

I'm looking to flag those rows that are >= 7 days from the previously identified record. From the example below, the valid rows are 4-13-2022, 4-20-2022 and 4-28-2022

drop table #tmp1
create table #tmp1(
membernum int,
sevrfromdate date
)

insert into #tmp1
values (1000016, '4/13/2022')
insert into #tmp1
values (1000016, '4/15/2022')
insert into #tmp1
values (1000016, '4/20/2022')
insert into #tmp1
values (1000016, '4/22/2022')
insert into #tmp1
values (1000016, '4/23/2022')
insert into #tmp1
values (1000016, '4/28/2022')

This is my failed attempt to flag the records.

select *,
case when datediff(d,coalesce(lag(sevrfromdate) over (partition by membernum order by sevrfromdate),
                        dateadd(d, -7, sevrfromdate)), sevrfromdate) > = 5 then 1 else 0 end as lasttm,
                        lag(sevrfromdate) over (partition by membernum order by sevrfromdate)
from #tmp1

desired output

Answer 1

As pointed above by @siggemannen, there we can use recursive query to select first and next flagged date.

1. First, calculate possible next date for every row - min(sevrfromdate) where this date>=7 days from current row date.
2. Then we take first row (obviously flagged) and recursively take other flagged rows.
3. Finally, join source table with recursive query output.

See example

membernum	sevrfromdate
1000016	2022-04-13
1000016	2022-04-15
1000016	2022-04-20
1000016	2022-04-22
1000016	2022-04-23
1000016	2022-04-28

with t as(
  select membernum, sevrfromdate
    ,(select min(sevrfromdate)
      from #tmp1 t2 where t2.membernum=t.membernum
       and t2.sevrfromdate>=dateadd(d,7,t.sevrfromdate)
  )  nextdate
  from #tmp1 t

)
,r as(
  select 0 lvl, membernum, sevrfromdate
    , nextdate
  from  t
  where sevrfromdate=(select min(sevrfromdate)
             from #tmp1 t2 where t2.membernum=t.membernum) 
  union all
  select lvl+1,t.membernum, t.sevrfromdate
    ,t.nextdate
  from r inner join t on r.membernum=t.membernum
     and t.sevrfromdate=r.nextdate
) 
select t.membernum, t.sevrfromdate
  ,case when r.sevrfromdate is not null then 1 else 0 end flag
  ,r.nextdate as next_date
from #tmp1 t
left join r on r.membernum=t.membernum
   and r.sevrfromdate=t.sevrfromdate

membernum	sevrfromdate	flag	next_date
1000016	2022-04-13	1	2022-04-20
1000016	2022-04-15	0	null
1000016	2022-04-20	1	2022-04-28
1000016	2022-04-22	0	null
1000016	2022-04-23	0	null
1000016	2022-04-28	1	null

fiddle

Recursive query result

lvl	membernum	sevrfromdate	nextdate
0	1000016	2022-04-13	2022-04-20
1	1000016	2022-04-20	2022-04-28
2	1000016	2022-04-28	null