CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Futochan
Futochan

Reputation: 331

Group by min and max date for each ID

I am having a hard time on SQL. Hoping everyone can help me on this. Thanks!

I have the following dataset:

Mike 1/1/2019  4/30/2019  
Mike 5/1/2019  7/31/2019  
Mike 11/1/2019 12/31/2019  
Jen  3/1/2019  5/31/2019  
Jen  6/1/2019  7/31/2019

How do I write the SQL script so I can get the following result? 

Mike 1/1/2019 7/31/2019  
Mike 11/1/2019 12/31/2019  
Jen 3/1/2019 7/31/2019

I tried writing the following SQL script but it's not giving me what I want. 

Select Name, min(startdate) as start, max(enddate) as end
from testtable
group by Name

I get the following instead:

Mike 1/1/2019 12/31/2019  
Jen 3/1/2019  7/31/2019

Upvotes: 2

Views: 1179

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

This is a type of gaps and islands problem. I like to solve this by getting the cumulative maximum enddate up to each record. A new "island" starts when this end date has a gap from the current start date:

select name, grp, min(startdate), max(enddate)
from (select t.*,
             sum(case when prev_enddate >= dateadd(day, -1, startdate) then 0 else 1 end) over
                 (partition by name order by startdate) as grp
      from (select t.*,
                   max(enddate) over (partition by name
                                      order by startdate
                                      rows between unbounded preceding and 1 preceding
                                     ) as prev_enddate
            from t
           ) t
     ) t
group by name, grp;

Here is a db<>fiddle.

Upvotes: 1

Related Questions