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 |