Write a SQL query that detects whether a proposed booking overlaps an existing booking for the same resource.

Table: Bookings(BookingId, ResourceId, StartsAt, EndsAt, Status). Parameters describe a proposed [StartsAt, EndsAt) booking and may include @ExistingBookingId during an edit. Return conflicting active bookings for the same resource. A booking ending exactly when another starts is allowed.

An existing booking is [10:00, 11:00). Proposals [10:30, 11:30) and [09:30, 10:30) conflict. [11:00, 12:00) does not.

• Reject or validate @StartsAt >= @EndsAt before using this query.
• Cancelled bookings do not conflict.
• When editing, exclude the current booking id.
• Intervals use an inclusive start and exclusive end.

Two half-open intervals overlap when each starts before the other ends. That becomes existing.StartsAt < proposed.EndsAt and existing.EndsAt > proposed.StartsAt. I keep both comparisons strict so touching boundaries are allowed. The query detects a conflict, but preventing races also requires the write path to serialize competing reservations or enforce the rule through a suitable database design.

SELECT b.BookingId, b.StartsAt, b.EndsAt
FROM dbo.Bookings AS b
WHERE b.ResourceId = @ResourceId
  AND b.Status IN ('Pending', 'Confirmed')
  AND b.StartsAt < @EndsAt
  AND b.EndsAt > @StartsAt
  AND (@ExistingBookingId IS NULL OR b.BookingId <> @ExistingBookingId)
ORDER BY b.StartsAt, b.BookingId;

An index beginning with ResourceId and including the time and status columns narrows the search, although two-sided interval predicates cannot usually seek perfectly on both boundaries. For dense booking calendars, a slot model or stronger reservation strategy may be easier to enforce.

• A proposal touching the start or end boundary.
• A proposal fully containing an existing booking.
• An existing booking fully containing the proposal.
• A cancelled booking in the same period.

• Test all four overlap shapes, not only a start inside an existing range.
• Test equal boundaries to verify half-open behavior.
• Edit a booking without excluding itself.
• Run two simultaneous reservation attempts when testing the write workflow.

• Checking only whether the proposed start is inside an existing range.
• Using BETWEEN and rejecting bookings that merely touch.
• Counting cancelled bookings as conflicts.
• Assuming a read-before-write check alone prevents concurrent overlaps.