SQL Server环境下酒店客房可用日期范围查询及验证需求

Alright, let's break down how to solve this hotel room availability problem in SQL Server. First, let's start with a common table structure for bookings—this is what most hotel systems use, so adjust it if your schema is a bit different:

CREATE TABLE RoomBookings (
    BookingID INT PRIMARY KEY IDENTITY,
    RoomNumber VARCHAR(10) NOT NULL, -- Example: '101', '202A'
    CheckInDate DATE NOT NULL,
    CheckOutDate DATE NOT NULL,
    -- Add other columns like GuestName, BookingStatus, etc. as needed
);

This query will pull all gaps between existing bookings, plus the periods before the first booking and after the last booking (within your desired date window). We'll use window functions to compare consecutive bookings:

-- Configure your parameters
DECLARE @RoomNumber VARCHAR(10) = '101';
DECLARE @StartDate DATE = '2024-01-01'; -- Optional: filter from this date
DECLARE @EndDate DATE = '2024-12-31'; -- Optional: filter up to this date

WITH BookingsOrdered AS (
    SELECT 
        RoomNumber,
        CheckInDate,
        CheckOutDate,
        -- Get the next booking's check-in date for the same room
        LEAD(CheckInDate) OVER (PARTITION BY RoomNumber ORDER BY CheckInDate) AS NextCheckIn
    FROM RoomBookings
    WHERE RoomNumber = @RoomNumber
      AND CheckOutDate >= @StartDate -- Ignore bookings that ended before our window starts
      AND CheckInDate <= @EndDate -- Ignore bookings that start after our window ends
),
AvailableRanges AS (
    -- Handle the period before the first booking
    SELECT 
        @RoomNumber AS RoomNumber,
        @StartDate AS AvailableStart,
        MIN(CheckInDate) AS AvailableEnd
    FROM BookingsOrdered
    UNION ALL
    -- Handle gaps between back-to-back bookings
    SELECT 
        RoomNumber,
        CheckOutDate AS AvailableStart,
        NextCheckIn AS AvailableEnd
    FROM BookingsOrdered
    WHERE NextCheckIn > CheckOutDate -- Only include actual gaps
    UNION ALL
    -- Handle the period after the last booking
    SELECT 
        @RoomNumber AS RoomNumber,
        MAX(CheckOutDate) AS AvailableStart,
        @EndDate AS AvailableEnd
    FROM BookingsOrdered
)
-- Filter out ranges with no actual available days
SELECT 
    RoomNumber,
    AvailableStart,
    AvailableEnd,
    DATEDIFF(day, AvailableStart, AvailableEnd) AS TotalAvailableDays
FROM AvailableRanges
WHERE AvailableStart < AvailableEnd
ORDER BY AvailableStart;

How This Works: #

• The BookingsOrdered CTE sorts bookings for the room and uses LEAD() to grab the next booking's check-in date.
• The AvailableRanges CTE combines three scenarios:
  1. Time from your start date to the first booking's check-in.
  2. Gaps between the end of one booking and the start of the next.
  3. Time from the last booking's check-out to your end date.
• Finally, we filter out any ranges where the start date is equal to or later than the end date (these would be zero-day gaps).

To check if a room is free for your desired check-in/check-out dates, we just need to look for overlapping bookings. The key overlap condition is critical here—bookings overlap if the existing booking starts before your desired check-out, and ends after your desired check-in.

-- Configure your parameters
DECLARE @RoomNumber VARCHAR(10) = '101';
DECLARE @DesiredCheckIn DATE = '2024-05-10';
DECLARE @DesiredCheckOut DATE = '2024-05-15';

IF NOT EXISTS (
    SELECT 1
    FROM RoomBookings
    WHERE RoomNumber = @RoomNumber
      -- Core overlap check: existing booking overlaps with desired range
      AND CheckInDate < @DesiredCheckOut
      AND CheckOutDate > @DesiredCheckIn
)
BEGIN
    PRINT 'Room ' + @RoomNumber + ' is available for the requested dates.';
END
ELSE
BEGIN
    PRINT 'Room ' + @RoomNumber + ' is NOT available for the requested dates.';
END

Why This Overlap Check Works: #

This covers all possible overlap scenarios:

• Your desired range is entirely within an existing booking.
• Your desired range starts before an existing booking ends and ends after it starts.
• Your desired range starts during an existing booking.
• Your desired range ends during an existing booking.

• If your system treats CheckOutDate as the first available date (e.g., a guest checks out on 5/15, so the room is available for check-in on 5/15), this logic works perfectly because the overlap check won't flag a conflict.
• To check availability for all rooms, remove the RoomNumber = @RoomNumber filter from the queries.
• For reusability, wrap these queries in stored procedures with parameters for room number and dates.

内容的提问来源于stack exchange，提问作者Mahesh Gunawardana