SQL Server 2019

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

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');
6 rows affected

SELECT * FROM #Times


Name
Surname
StartDate
EndDate


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


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] 


Name
Surname
StartDate
EndDate


Andrew
Green
2021-07-02 08:30:00.000
2021-07-03 09:00:00.000

Mary
Green
2021-07-01 00: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

Paul
Brown
2021-07-04 06:00:00.000
2021-07-04 09:00:00.000


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 


StartDate
EndDate


2021-07-01 00:00:00.000
2021-07-03 09:00:00.000

2021-07-04 06:00:00.000
2021-07-04 09:00:00.000

Name	Surname	StartDate	EndDate
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