clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36284 in the last week).

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


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)