您有以下案例

The user's selected period:
--------[---------]-------
Booking no1 
[-----]-------------------
Booking no2
--------------------[----]
Booking no3
-----[----]---------------
Booking no4
-----------[---]----------
Booking no5
------[-------]-----------
Booking no6
--------------[--------]--
Booking no7
-----[----------------]---

你将不得不找出哪些时期会交叉。显然，案例1和案例2是免费的。案例3、5、6很容易捕捉，因为如果预订的开始日期或结束日期在用户的选择范围内，您可以进行搜索。案例4和案例7您需要确定用户的选择日期是否在预订之间。

因此，以下内容可以找到空闲房间：

DECLARE @ArrivalDate AS DATETIME
DECLARE @DepartureDate AS DATETIME

SELECT RoomType 
FROM ROOMS 
WHERE RoomID NOT IN 
(
    SELECT RoomID 
    FROM   BOOKING B
           JOIN ROOMS_BOOKED RB
               ON B.BookingID = RB.BookingID
    WHERE  (ArrivalDate <= @ArrivalDate AND DepartureDate >= @ArrivalDate) -- cases 3,5,7
           OR (ArrivalDate < @DepartureDate AND DepartureDate >= @DepartureDate ) --cases 6,6
           OR (@ArrivalDate <= ArrivalDate AND @DepartureDate >= ArrivalDate) --case 4
)

此查询列出所有房间，并显示每个房间在[到达，出发]日期内是否可用

SELECT 
    RoomType,
    case when NOT EXISTS (SELECT RoomID 
                  FROM ROOMS_BOOKED rb 
                  JOIN BOOKING b on b.BookingID = rb.BookingID
                  WHERE rb.RoomID = r.Id 
                    and ArrivalDate < 'param Departure Date here'
                    and DepartureDate > 'param Arrival Date here')
        then 1 else 0 end IsAvailable
FROM ROOMS r

当满足以下两个条件时，就会发生预订冲突

1.已有记录签入日期<inComingRequestcheckout 日期
1.已有记录checkout 日期>来电申请签入日期
注意：现有记录CheckinInDates与传入请求的CheckoutDate进行比较，反之亦然

让我们假设传入请求参数：

SET @checkInDate := '2022-09-14'; -- incoming req.checkInDate
 SET @checkOutDate := '2022-09-17'; -- incoming req.checkOutDate

包含一些现有预订的预订表：

用于获取所有冲突预订的实际查询：

select * from BOOKING B where 
B.checkInDate < @checkOutDate
AND B.checkOutDate > @checkInDate

输出(所有冲突的预订)：

booking_id  checkInDate checkOutDate
       1    2022-09-1   2022-09-30
       2    2022-09-12  2022-09-15
       3    2022-09-15  2022-09-18
       6    2022-09-14  2022-09-17

Bookking_id 4被排除在外，因为已有预订的客户在该客户登记入住时刚从酒店退房。

Bookking_id 5被排除在外，因为已有预订的客户将在该客户退房时入住酒店。

因此，bookking_id 4和5实际上并不与传入请求冲突

以下是我用来检查不可用房间的示例查询，该查询将返回不可用房间

SELECT roomno FROM tbl_ReservedRooms WHERE ((fromdate BETWEEN @checkindate AND @checkoutdate )
OR (todate BETWEEN @checkindate AND @checkoutdate)) 
AND status = 'Active'

SELECT*FROM ROOM RM WHERE rm.Room_id NOT IN(SELECT DISTINCT Room_id from Booking bk WHERE(bk.到站日期介于‘$From_Date’和‘$To_Date’之间))OR(bk.到达日<‘$From_Date’AND bk.Designment_Date>‘$To_Date’))