insert into #t1
select '1','7/1/2021'
union all select '1','7/2/2021'
union all select '3','7/3/2021'
union all select '3','7/4/2021'
union all select '3','7/5/2021'
union all select '3','7/6/2021'
union all select '3','7/7/2021'
union all select '3','7/8/2021'
union all select '1','7/9/2021'
union all select '1','7/10/2021'
union all select '1','7/11/2021'
union all select '1','7/12/2021'
I tried using the lead/row_number function, but since the ID's are re-used after few days, I am not able to reset the counter..like in above eg : 7/9, ID 1 gets active again..so I need to reset whatever counter I have back to 1..
like below:
;with cte as (select id, effdate, row_number() over (partition by id order by effdate) - row_number() over (order by effdate) as grp
from #t1 )
select id, min(effdate) as FromDate, max(EffDate) as toDate
from cte group by grp, id
order by FromDate, toDate