Your next meeting needs a gap that actually fits inside the working day. Free time slots can be generated on a fixed grid or calculated as arbitrary gaps. Both approaches need consistent interval boundaries.
Use Half-Open Appointment Intervals
Represent appointments as starting at StartAt and ending just before EndAt. Under that convention, an appointment ending at ten does not overlap one starting at ten. This prevents adjacent bookings from appearing to conflict only because they share a boundary.
I define that convention before writing the anti-overlap predicate. Mixing inclusive and exclusive endpoints produces confusing availability at exactly the time readers want to book. The sample uses one local time zone and datetime2 values for a single day. It does not model daylight-saving transitions.
CREATE TABLE #Bookings
(BookingID int PRIMARY KEY,StartAt datetime2(0) NOT NULL,
EndAt datetime2(0) NOT NULL,CHECK(StartAt<EndAt));
INSERT #Bookings VALUES
(1,'2026-09-26T09:15:00','2026-09-26T10:00:00'),
(2,'2026-09-26T11:00:00','2026-09-26T12:00:00'),
(3,'2026-09-26T11:30:00','2026-09-26T13:00:00'),
(4,'2026-09-26T15:00:00','2026-09-26T15:45:00');The overlapping middle bookings are intentional. They make the arbitrary-gap query prove it handles overlap instead of assuming a clean calendar. Do not reject real existing overlaps by silently dropping one booking. Availability must exclude the union of every busy interval.
Generate Thirty-Minute Free Time Slots
SQL Server 2022 with compatibility level 160 supports GENERATE_SERIES. Generate start offsets from the beginning of working hours, then add the requested duration. The last start must leave room for the complete slot before closing.
DECLARE @WorkStart datetime2(0)='2026-09-26T09:00:00',
@WorkEnd datetime2(0)='2026-09-26T17:00:00';
WITH Slots AS
(
SELECT DATEADD(minute,value*30,@WorkStart) AS SlotStart,
DATEADD(minute,value*30+30,@WorkStart) AS SlotEnd
FROM GENERATE_SERIES(0,DATEDIFF(minute,@WorkStart,@WorkEnd)/30-1,1)
)
SELECT SlotStart,SlotEnd FROM Slots s
WHERE NOT EXISTS
(SELECT 1 FROM #Bookings b
WHERE b.StartAt<s.SlotEnd AND b.EndAt>s.SlotStart)
ORDER BY SlotStart;This grid starts at the working-day boundary. A gap beginning at ten fifteen does not become a ten-fifteen slot under this expression. Decide whether that alignment matches the booking interface. If appointments can start at any minute, the arbitrary-gap approach supplies a different answer.
Validate that working hours are increasing and the requested duration is positive. The example's fixed eight-hour day divides evenly into thirty-minute slots. Other durations can leave an unusable tail, which should be excluded deliberately rather than treated as a full appointment.
Understand the Overlap Predicate
Two half-open intervals overlap when the existing start is before the candidate end and the existing end is after the candidate start. Both inequalities are strict. That preserves the rule that touching endpoints are allowed.
The predicate catches bookings contained in the candidate, candidates contained in bookings, and partial overlap on either side. Testing only whether the candidate start lies inside a booking misses those other cases. NOT EXISTS then keeps candidates without any overlapping booking.
Check boundary examples explicitly. A booking ending at the slot start should not exclude it. A booking starting at the slot end should not exclude it either. A booking one minute inside either boundary should exclude it. These small tests describe the intended interval contract more clearly than an unexplained BETWEEN expression.
Clip Busy Time to Working Hours
For arbitrary gaps, first retain bookings intersecting the working day. Clip their boundaries to working hours so a booking starting earlier or ending later cannot create an out-of-hours gap. The next temporary table contains only the portion relevant to this report.
DECLARE @WorkStart datetime2(0)='2026-09-26T09:00:00',
@WorkEnd datetime2(0)='2026-09-26T17:00:00';
SELECT BookingID,
CASE WHEN StartAt<@WorkStart THEN @WorkStart ELSE StartAt END AS StartAt,
CASE WHEN EndAt>@WorkEnd THEN @WorkEnd ELSE EndAt END AS EndAt
INTO #ClippedBookings
FROM #Bookings WHERE StartAt<@WorkEnd AND EndAt>@WorkStart;A direct LEAD over raw appointments is unsafe when bookings overlap or nest. An earlier long appointment can extend beyond the next appointment's end. Looking only at neighboring rows can then manufacture a false gap. Merge the busy union first.
Merge Overlapping Busy Intervals
Compute the maximum ending boundary of all preceding bookings. A new island begins only when the next start lies beyond that prior maximum. This handles nested intervals that a simple LAG of EndAt would miss. BookingID supplies deterministic ordering when starts and ends tie.
WITH Prior AS
(
SELECT *,MAX(EndAt) OVER
(ORDER BY StartAt,EndAt,BookingID
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PriorEnd
FROM #ClippedBookings
), Marks AS
(
SELECT *,CASE WHEN PriorEnd IS NULL OR StartAt>PriorEnd
THEN 1 ELSE 0 END AS NewIsland FROM Prior
), Groups AS
(
SELECT *,SUM(NewIsland) OVER
(ORDER BY StartAt,EndAt,BookingID ROWS UNBOUNDED PRECEDING) AS IslandID
FROM Marks
)
SELECT MIN(StartAt) AS StartAt,MAX(EndAt) AS EndAt
INTO #BusyIslands FROM Groups GROUP BY IslandID;Touching busy intervals can be merged because they leave no positive-duration gap. That choice is consistent with half-open booking boundaries. The merged result describes busy coverage, not a new appointment record. Retain original bookings for ownership and detail views.
Use LEAD to Find Free Time Slots of Any Length
Add zero-length boundary markers at opening and closing. Then LEAD supplies the next busy start after each busy end. Positive intervals between them are free. The markers also handle a day with no bookings, producing the complete working period.
DECLARE @WorkStart datetime2(0)='2026-09-26T09:00:00',
@WorkEnd datetime2(0)='2026-09-26T17:00:00';
WITH Boundaries AS
(
SELECT StartAt,EndAt FROM #BusyIslands
UNION ALL SELECT @WorkStart,@WorkStart
UNION ALL SELECT @WorkEnd,@WorkEnd
), NextStarts AS
(
SELECT EndAt AS GapStart,
LEAD(StartAt) OVER(ORDER BY StartAt,EndAt) AS GapEnd
FROM Boundaries
)
SELECT GapStart,GapEnd,DATEDIFF(minute,GapStart,GapEnd) AS FreeMinutes
FROM NextStarts WHERE GapEnd>GapStart ORDER BY GapStart;Filter these gaps by the required appointment duration if the consumer needs bookable ranges. A fifteen-minute gap is free time but cannot hold a thirty-minute meeting. I keep those two facts separate in the report. Which result does the interface need: aligned starts, complete free ranges, or both?
With the sample bookings, the grid query returned eight free half-hour slots. The gap query returned 09:00 to 09:15, 10:00 to 11:00, 13:00 to 15:00, and 15:45 to 17:00. The grid has no 15:45 start because its slots align to 09:00.
Address Time Zones and Concurrent Bookings
Datetime2 values do not carry a time-zone offset. For cross-zone scheduling, use a documented zone conversion and datetimeoffset where offsets matter. A daylight-saving day does not always contain the usual elapsed number of hours. Generate intervals using the business zone's rules instead of assuming every date has identical duration.
An availability query is also a snapshot, not a reservation. Another session can book a returned interval before the caller commits its choice. The booking operation needs its own concurrency design and conflict check. A clean list of gaps does not enforce uniqueness of reservations.
Validate Free Time Slots Against the Calendar Contract
Test an empty calendar, full-day booking, overlapping bookings, nested bookings, and appointments crossing working-hour boundaries. Include equal endpoints and an appointment shorter than the slot grid. Verify the merged gaps stay inside the day and intersect no busy interval.
Keep indexes and predicates focused on the relevant resource and date range in a real calendar. The sample has one resource, but a room or person identifier belongs in production filters and grouping. Free time is defined for a specific calendar, not every appointment table combined indiscriminately.
For multiple calendars, merge busy intervals within each resource separately. A booking for one room should not consume another room's availability. Include the resource identifier in the running maximum, island grouping, and final gap calculation. Apply access permissions to the calendar data as well. A technically available slot still needs the caller to be authorized to reserve the resource it represents.
Free time slots are availability results, not committed reservations. Recheck conflicts during booking so free time slots cannot be mistaken for a concurrency guarantee.
Related reading on this blog: Gaps and Islands: Finding Missing Ranges in a Sequence and Preventing Overlapping Date Ranges in a Table.
Free time is not a gap between arbitrary neighboring rows, it is the part of working hours outside the complete union of bookings.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
