SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab1 (
    id int,
    startdate date,
    enddate date
);

INSERT INTO tab1 (id, startdate, enddate)
VALUES 
  (1,'2000-08-08', '2019-03-31'),
  (1,'2007-06-08', '2007-09-09'), 
  (1,'2008-02-08', '2011-08-04'), 
  (1,'2012-08-01', '2012-09-20'),
  (1,'2018-02-01', '2019-03-31'),
  (2,'2013-02-18', '2019-03-28'),  
  (2,'2018-04-12', '2020-07-28'),
  (3,'2015-01-11', '2015-04-02'),  
  (3,'2016-02-08', '2021-11-22')
  

select *
from tab1
order by id, startdate
  


id
startdate
enddate


1
2000-08-08
2019-03-31

1
2007-06-08
2007-09-09

1
2008-02-08
2011-08-04

1
2012-08-01
2012-09-20

1
2018-02-01
2019-03-31

2
2013-02-18
2019-03-28

2
2018-04-12
2020-07-28

3
2015-01-11
2015-04-02

3
2016-02-08
2021-11-22


WITH cte1 AS (
  SELECT
    id,
    startdate,
    enddate,
    CASE
      WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= DATEADD(day, -1, startdate) THEN 0
      ELSE 1
    END AS new_grp
  FROM tab1
), cte2 AS (
  SELECT
    cte1.*,
    SUM(new_grp) OVER (PARTITION BY id ORDER BY startdate) AS grp_num
  FROM cte1
)
SELECT
  id,
  MIN(startdate) AS startdate,
  MAX(enddate) AS enddate
FROM cte2
GROUP BY id, grp_num
ORDER BY id, startdate;


id
startdate
enddate


1
2000-08-08
2019-03-31

1
2008-02-08
2011-08-04

1
2012-08-01
2012-09-20

1
2018-02-01
2019-03-31

2
2013-02-18
2020-07-28

3
2015-01-11
2015-04-02

3
2016-02-08
2021-11-22


CREATE TABLE tab2 (
    id int,
    startdate date,
    enddate date
);

INSERT INTO tab2 (id, startdate, enddate)
VALUES 
  (1,'2000-08-08', '2019-03-31'),
  (1,'2008-02-08', '2011-08-04'), 
  (1,'2012-08-01', '2012-09-20'),
  (1,'2018-02-01', '2019-03-31'),
  (2,'2013-02-18', '2019-03-28'),
  (3,'2015-01-11', '2015-04-02'),  
  (3,'2016-02-08', '2021-11-22')
7 rows affected

WITH cte1 AS (
  SELECT
    id,
    startdate,
    enddate,
    CASE
      WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= DATEADD(day, -1, startdate) THEN 0
      ELSE 1
    END AS new_grp
  FROM tab2
), cte2 AS (
  SELECT
    cte1.*,
    SUM(new_grp) OVER (PARTITION BY id ORDER BY startdate) AS grp_num
  FROM cte1
)
SELECT
  id,
  MIN(startdate) AS startdate,
  MAX(enddate) AS enddate
FROM cte2
GROUP BY id, grp_num
ORDER BY id, startdate;


id
startdate
enddate


1
2000-08-08
2019-03-31

1
2012-08-01
2012-09-20

1
2018-02-01
2019-03-31

2
2013-02-18
2019-03-28

3
2015-01-11
2015-04-02

3
2016-02-08
2021-11-22


CREATE TABLE tab3 (
    id int,
    startdate date,
    enddate date
);

INSERT INTO tab3 (id, startdate, enddate)
VALUES 
  (1,'2000-08-08', '2019-03-31'),
  (1,'2012-08-01', '2012-09-20'),
  (1,'2018-02-01', '2019-03-31'),
  (2,'2013-02-18', '2019-03-28'),
  (3,'2015-01-11', '2015-04-02'),  
  (3,'2016-02-08', '2021-11-22')

6 rows affected

WITH cte1 AS (
  SELECT
    id,
    startdate,
    enddate,
    CASE
      WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= DATEADD(day, -1, startdate) THEN 0
      ELSE 1
    END AS new_grp
  FROM tab3
), cte2 AS (
  SELECT
    cte1.*,
    SUM(new_grp) OVER (PARTITION BY id ORDER BY startdate) AS grp_num
  FROM cte1
)
SELECT
  id,
  MIN(startdate) AS startdate,
  MAX(enddate) AS enddate
FROM cte2
GROUP BY id, grp_num
ORDER BY id, startdate;


id
startdate
enddate


1
2000-08-08
2019-03-31

1
2018-02-01
2019-03-31

2
2013-02-18
2019-03-28

3
2015-01-11
2015-04-02

3
2016-02-08
2021-11-22


CREATE TABLE tab4 (
    id int,
    startdate date,
    enddate date
);

INSERT INTO tab4 (id, startdate, enddate)
VALUES 
  (1,'2000-08-08', '2019-03-31'),
  (1,'2018-02-01', '2019-03-31'),
  (2,'2013-02-18', '2019-03-28'),
  (3,'2015-01-11', '2015-04-02'),  
  (3,'2016-02-08', '2021-11-22')
5 rows affected

WITH cte1 AS (
  SELECT
    id,
    startdate,
    enddate,
    CASE
      WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= DATEADD(day, -1, startdate) THEN 0
      ELSE 1
    END AS new_grp
  FROM tab4
), cte2 AS (
  SELECT
    cte1.*,
    SUM(new_grp) OVER (PARTITION BY id ORDER BY startdate) AS grp_num
  FROM cte1
)
SELECT
  id,
  MIN(startdate) AS startdate,
  MAX(enddate) AS enddate
FROM cte2
GROUP BY id, grp_num
ORDER BY id, startdate;


id
startdate
enddate


1
2000-08-08
2019-03-31

2
2013-02-18
2019-03-28

3
2015-01-11
2015-04-02

3
2016-02-08
2021-11-22
id	startdate	enddate
1	2000-08-08	2019-03-31
1	2007-06-08	2007-09-09
1	2008-02-08	2011-08-04
1	2012-08-01	2012-09-20
1	2018-02-01	2019-03-31
2	2013-02-18	2019-03-28
2	2018-04-12	2020-07-28
3	2015-01-11	2015-04-02
3	2016-02-08	2021-11-22