SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #ExampelTable (Id INT IDENTITY(1,1), PersonId INT, FromDate DATE, ToDate DATE)
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2009-01-01','2009-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-01-26','2010-01-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-02-01','2010-06-20')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-06-21','2011-02-17')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2011-02-18','2011-07-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2013-12-03','2014-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2015-11-03','2016-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (20, '2020-11-03','2021-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-03','2000-11-25')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-26','2000-11-30')
10 rows affected

with grps as (
  select id, personid, fromdate, todate, 
          sum(grp) over (partition by personid order by fromdate) grp
  from (
    select et.*, 
           case when lag(todate) over (partition by personid order by fromdate) 
                   = dateadd(dd, -1, fromdate) 
                then 0 else 1 end grp
    from #ExampelTable et) a )
select id, personid, fromdate, todate, 
       min(fromdate) over (partition by personid, grp) d1, 
       max(todate) over (partition by personid, grp) d2 
  from grps


id
personid
fromdate
todate
d1
d2


1
10
2009-01-01
2009-06-30
2009-01-01
2009-06-30

2
10
2010-01-26
2010-01-31
2010-01-26
2011-07-31

3
10
2010-02-01
2010-06-20
2010-01-26
2011-07-31

4
10
2010-06-21
2011-02-17
2010-01-26
2011-07-31

5
10
2011-02-18
2011-07-31
2010-01-26
2011-07-31

6
10
2013-12-03
2014-06-30
2013-12-03
2014-06-30

7
10
2015-11-03
2016-06-30
2015-11-03
2016-06-30

8
20
2020-11-03
2021-06-30
2020-11-03
2021-06-30

9
30
2000-11-03
2000-11-25
2000-11-03
2000-11-30

10
30
2000-11-26
2000-11-30
2000-11-03
2000-11-30

id	personid	fromdate	todate	d1	d2
1	10	2009-01-01	2009-06-30	2009-01-01	2009-06-30
2	10	2010-01-26	2010-01-31	2010-01-26	2011-07-31
3	10	2010-02-01	2010-06-20	2010-01-26	2011-07-31
4	10	2010-06-21	2011-02-17	2010-01-26	2011-07-31
5	10	2011-02-18	2011-07-31	2010-01-26	2011-07-31
6	10	2013-12-03	2014-06-30	2013-12-03	2014-06-30
7	10	2015-11-03	2016-06-30	2015-11-03	2016-06-30
8	20	2020-11-03	2021-06-30	2020-11-03	2021-06-30
9	30	2000-11-03	2000-11-25	2000-11-03	2000-11-30
10	30	2000-11-26	2000-11-30	2000-11-03	2000-11-30