Help with Query

Other SQL Server Topics
1

create table #t1 (id int, effdate date)

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 need a consolidated data like:

ID startdate enddate
1 7/1/2021 7/2/2021
3 7/3/2021 7/8/2021
1 7/9/2021 null

2

Explain the logic. We all need something but...

3

standard "islands" problem.

4

sorry..
I need to go by the ID and the effdate:

ID 1 is effective from 7/1 till 7/2
then ID 3 gets active from 7/3 till 7/8
then ID 1 again gets active from 7/10 till 7/12

so the o/p I was expecting was:

ID startdate enddate
1 7/1/2021 7/2/2021
3 7/3/2021 7/8/2021
1 7/9/2021 null

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:

ID date Counter
1 2021-07-01 1
1 2021-07-02 2
3 2021-07-03 1
3 2021-07-04 2
3 2021-07-05 3
3 2021-07-06 4
3 2021-07-07 5
3 2021-07-08 6
1 2021-07-09 1
1 2021-07-10 2
1 2021-07-11 3
1 2021-07-12 4

5 
;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
3 Likes
6

Thank you Mike01!!!!

This looks perfect..Thank you so much!!!