How to aggregate periods in SQL Server

Example 1

Starting from the #Times temporary table

CREATE TABLE #Times(
	[Name] [nvarchar](50) NOT NULL,
	[Surname] [nvarchar](50) NOT NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
) ON [PRIMARY];

INSERT INTO #Times
  ( [Name], [Surname], [StartDate], [EndDate] )
VALUES
  ('Mary', 'Green', '2021-07-01 00:00:00.000', '2021-07-01 14:00:00.000'),
  ('Mary', 'Green', '2021-07-01 14:00:00.000', '2021-07-02 08:30:00.000'),
  ('Mary', 'Green', '2021-07-03 00:00:00.000', '2021-07-03 08:00:00.000'),
  ('Andrew', 'Green', '2021-07-02 08:30:00.000', '2021-07-03 08:45:00.000'),
  ('Andrew', 'Green', '2021-07-03 08:45:00.000', '2021-07-03 09:00:00.000'),
  ('Paul', 'Brown', '2021-07-04 06:00:00.000', '2021-07-04 09:00:00.000');

you can aggregate contiguous periods of the same Name/Surname, with just the following

SELECT
  A1.[Name],A1.[Surname],A1.[StartDate],MIN(B1.[EndDate]) AS [EndDate]
FROM #Times A1
INNER JOIN #Times B1
  ON A1.[Name] = B1.[Name] and A1.[Surname]=B1.[Surname] and A1.[StartDate] <= B1.[EndDate]
AND NOT EXISTS(
  SELECT * FROM #Times B2
  WHERE B1.[Name] = B2.[Name] and B1.[Surname]=B2.[Surname] and B1.[EndDate] >= B2.[StartDate]
  AND B1.[EndDate] < B2.[EndDate]
)
WHERE NOT EXISTS(
  SELECT * FROM #Times A2
  WHERE A1.[Name] = A2.[Name] and A1.[Surname]=A2.[Surname] and A1.[StartDate] > A2.[StartDate]
  AND A1.[StartDate] <= A2.[EndDate]
)
GROUP BY A1.[Name],A1.[Surname],A1.[StartDate]
ORDER BY A1.[Name],A1.[Surname],A1.[StartDate] 

You can find an interactive version of this example following this link .

Example 2

Based on the #Times previous temporary table, we can aggregate contiguous periods without involving the Name/Surname details.

SELECT
       A1.StartDate,MIN(B1.EndDate) AS EndDate
FROM #Times A1
INNER JOIN #Times B1 ON A1.StartDate <= B1.EndDate
  AND NOT EXISTS(SELECT * FROM #Times B2
                 WHERE B1.EndDate >= B2.StartDate AND B1.EndDate < B2.EndDate)
WHERE NOT EXISTS(SELECT * FROM #Times s2
                 WHERE A1.StartDate > s2.StartDate AND A1.StartDate <= s2.EndDate)
GROUP BY A1.StartDate
ORDER BY A1.StartDate 

You can find an interactive version of this example following this link .