SQL查询指定日期区间可用房间时checkUt空值处理逻辑实现

我需要编写SQL查询校验房间（瑞典语为rum）在两个日期之间是否可用，要求优先判断房间是否已退房：若checkUt（退房日期）非空，就使用fDatum和checkUt判断日期是否重叠；若checkUt为null，则使用fDatum和tDatum判断。
数据库中存储了房间的预订起止日期（fDatum、tDatum），以及实际入住、退房日期（checkIn、checkUt）。
补充说明：查询需要输出2021-10-12至2021-10-14期间未被预订的房间号rumNr，以及房型rumTyp（单人、双人、家庭房）。
我当前使用的代码如下，但存在问题：如果客人早于2021-10-12退房，只要原预订的tDatum为2021-10-12，房间仍会被判定为已预订。虽然客人仍需支付2021-10-12的房费，但酒店希望可以将提前退房的房间再次出租。

Select rumNr, rumTyp
From Rum
Where RumNr NOT IN (Select RumNr
                    From BokningRum
                    Where fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    OR tDatum BETWEEN '2021-10-12' AND '2021-10-14')
GROUP BY rumNr
;

我期望实现的逻辑如下：

Select rumNr, rumTyp
From Rum
Where RumNr NOT IN (Select RumNr
From BokningRum
Where fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    OR checkUt BETWEEN '2021-10-12' AND '2021-10-14')
                    -- 若checkUt为null，则执行如下逻辑
                    -- Where fDatum BETWEEN '2021-10-12' AND '2021-10-14'
                    -- OR tDatum BETWEEN '2021-10-12' AND '2021-10-14')
GROUP BY rumNr
;

请问如何修改SQL语句才能正确实现上述需求？

你可以通过COALESCE函数动态获取每笔预订的实际占用结束日期，同时修正日期重叠的判断逻辑即可实现需求：

1. COALESCE(checkUt, tDatum)会优先取实际退房日期checkUt，若checkUt为空则取原预订结束日期tDatum，刚好匹配你的判断规则。
2. 原逻辑用BETWEEN判断会出现边界误差，正确的时间段重叠判断逻辑为：预订开始时间早于查询时段结束时间，且预订实际结束时间晚于查询时段开始时间，即可判定该时段房间被占用。

修改后的SQL如下：

SELECT rumNr, rumTyp
FROM Rum
WHERE RumNr NOT IN (
    SELECT RumNr
    FROM BokningRum
    WHERE 
        fDatum < '2021-10-14'
        AND COALESCE(checkUt, tDatum) > '2021-10-12'
)
-- 若Rum表中rumNr为唯一主键，可删除下方GROUP BY语句
GROUP BY rumNr, rumTyp;

如果你的数据库不支持COALESCE函数，可替换为等效的IF(checkUt IS NULL, tDatum, checkUt)逻辑。如果酒店有明确的退房时间（比如中午12点），可以把日期参数改为带时间的格式（如'2021-10-12 12:00:00'）实现更精准的判断。

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