Finding Schedule Clashes: Overlapping Sessions per Attendee

Schedule clashes are pairs of sessions that overlap in time for the same attendee. Each session looks fine alone, and the problem only shows up when you compare two of them. A self-join with a two-sided overlap test finds every pair exactly once.

Why every session looks fine on its own

An attendee emails the organizer: “I signed up for three sessions and two of them seem to run at the same time.” Each registration is valid. The schedule is only wrong when you put two of them side by side. That is a job for a query, not for eyeballing a spreadsheet.

First, the data. A session has a start and an end, and a check constraint keeps the end after the start. A registration is one attendee in one session. Attendee 10 signs up for all three sessions, and attendee 20 for sessions 1 and 3.

DROP TABLE IF EXISTS #Registrations;
DROP TABLE IF EXISTS #Sessions;

CREATE TABLE #Sessions
(SessionId int PRIMARY KEY, SessionName nvarchar(80),
 StartsAt datetime2 NOT NULL, EndsAt datetime2 NOT NULL,
 CHECK (EndsAt > StartsAt));

CREATE TABLE #Registrations
(AttendeeId int NOT NULL, SessionId int NOT NULL,
 PRIMARY KEY (AttendeeId, SessionId));

INSERT #Sessions VALUES
(1, N'Planning',  '2025-01-01T09:00:00', '2025-01-01T10:00:00'),
(2, N'Review',    '2025-01-01T09:30:00', '2025-01-01T10:30:00'),
(3, N'Follow-up', '2025-01-01T10:00:00', '2025-01-01T11:00:00');

INSERT #Registrations VALUES (10, 1), (10, 2), (10, 3), (20, 1), (20, 3);

The primary key on registrations matters. Without it, one attendee could register for the same session twice, and every clash could show up twice. Try it. The second insert fails with error 2627.

BEGIN TRY
    INSERT #Registrations VALUES (10, 1);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

Find the clashes with a two-sided overlap test

Two intervals overlap when the first one starts before the second one ends, and the second one starts before the first one ends. Both comparisons are strict. Join each attendee’s registrations to themselves, and demand that the first SessionId is smaller than the second. That removes pairing a session with itself, and it reports each pair once, not twice.

DROP TABLE IF EXISTS #ScheduleClashes;

SELECT a.AttendeeId,
       s1.SessionId AS FirstSessionId, s1.SessionName AS FirstSession,
       s2.SessionId AS SecondSessionId, s2.SessionName AS SecondSession
INTO #ScheduleClashes
FROM #Registrations AS a
JOIN #Registrations AS b ON b.AttendeeId = a.AttendeeId AND a.SessionId < b.SessionId
JOIN #Sessions AS s1 ON s1.SessionId = a.SessionId
JOIN #Sessions AS s2 ON s2.SessionId = b.SessionId
WHERE s1.StartsAt < s2.EndsAt AND s2.StartsAt < s1.EndsAt;

SELECT * FROM #ScheduleClashes
ORDER BY AttendeeId, FirstSessionId, SecondSessionId;

Attendee 10 has two clashes: Planning with Review, and Review with Follow-up. Planning and Follow-up touch at 10:00 but do not overlap, so they are not listed. Attendee 20 has no clash at all.

Summarize the clashes per attendee

The organizer wants one line per person. STRING_AGG lists the clashing pairs. I convert the text to nvarchar(max) so a long list does not hit a length limit, and I order the aggregation so the line reads the same every time.

SELECT AttendeeId,
       COUNT_BIG(*) AS ClashPairCount,
       STRING_AGG(CONVERT(nvarchar(max), CONCAT(FirstSession, N' / ', SecondSession)), N'; ')
         WITHIN GROUP (ORDER BY FirstSessionId, SecondSessionId) AS ConflictingChoices
FROM #ScheduleClashes
GROUP BY AttendeeId
ORDER BY AttendeeId;

Remember what the count means. It is the number of clashing pairs, not the number of affected sessions. Review is in both of attendee 10’s pairs, and it is still one session.

Touching is not a clash

The strict comparisons decide the edge cases. The query below runs the overlap test on five interval shapes. Partial overlap, one inside the other, and identical intervals all clash. Two intervals that only touch do not, and neither do two that are apart.

SELECT Shape,
       CASE WHEN S1 < E2 AND S2 < E1 THEN 'Clash' ELSE 'No clash' END AS Verdict
FROM (VALUES
  (1, 'Partial overlap',      CAST('09:00' AS time), CAST('10:00' AS time), CAST('09:30' AS time), CAST('10:30' AS time)),
  (2, 'One inside the other', '09:00', '11:00', '09:30', '10:00'),
  (3, 'Identical',            '09:00', '10:00', '09:00', '10:00'),
  (4, 'Touching',             '09:00', '10:00', '10:00', '11:00'),
  (5, 'Apart',                '09:00', '10:00', '11:00', '12:00')
) AS v(n, Shape, S1, E1, S2, E2)
ORDER BY n;

Please do not “fix” the predicate by switching to less-than-or-equal. Then attendee 20 gets a false clash between Planning and Follow-up, and the report starts crying wolf.

SELECT a.AttendeeId,
       SUM(CASE WHEN s1.StartsAt < s2.EndsAt AND s2.StartsAt < s1.EndsAt THEN 1 ELSE 0 END) AS StrictClashes,
       SUM(CASE WHEN s1.StartsAt <= s2.EndsAt AND s2.StartsAt <= s1.EndsAt THEN 1 ELSE 0 END) AS TouchingCounted
FROM #Registrations AS a
JOIN #Registrations AS b ON b.AttendeeId = a.AttendeeId AND a.SessionId < b.SessionId
JOIN #Sessions AS s1 ON s1.SessionId = a.SessionId
JOIN #Sessions AS s2 ON s2.SessionId = b.SessionId
GROUP BY a.AttendeeId
ORDER BY a.AttendeeId;

What the query cannot decide for you

Two sessions in different buildings may need a travel buffer. That is a policy, so name it in the report. If a session moves, recompute the clashes for everyone registered, because a registration can become a clash without any registration row changing.

Blocking a clash at booking time needs a transaction, not just a precheck. Two bookings can both pass the check and together create the conflict. Decide whether you block, ask, or warn, and keep that separate from the report. The demo removes its temp tables below.

DROP TABLE IF EXISTS #ScheduleClashes;
DROP TABLE IF EXISTS #Registrations;
DROP TABLE IF EXISTS #Sessions;

Compare the pairs once, and let the strict test settle the edges.

A clash report is not a schedule policy, it is a list of overlapping choices.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.