如何编写SQL查询获取指定日期范围内的缺勤记录

问题说明 #

需要从数据表中筛选出所有与指定日期范围存在重叠的缺勤记录，字段信息：

• Absence：VARCHAR2类型
• StartDate、EndDate：DATE类型，无空值

运行时传入绑定变量:startDate和:endDate，需包含所有满足以下条件的记录：缺勤时间段与指定日期范围有交集（包括指定日期范围落在缺勤时间段内的情况）。

原始数据表 #

示例验证 #

示例1 #

绑定变量：:startDate = '2021-12-30'，:endDate = '2022-09-25'
预期返回结果：

30112   29-DEC-21   31-DEC-21
129228  23-SEP-22   23-SEP-22

示例2 #

绑定变量：:startDate = '2021-10-07'，:endDate = '2022-11-30'
预期返回结果：

24042   08-OCT-21   08-OCT-21
24043   29-NOV-21   29-NOV-21

解决方案 #

使用Oracle SQL的时间段重叠判断逻辑，核心条件是判断两个时间段是否存在交集。对于缺勤时间段[StartDate, EndDate]和指定范围[:startDate, :endDate]，交集存在的判定公式为：StartDate <= :endDate AND EndDate >= :startDate。

对应的SQL语句如下（替换你的数据表名为实际表名）：

SELECT Absence, StartDate, EndDate
FROM 你的数据表名
WHERE StartDate <= :endDate
  AND EndDate >= :startDate;

逻辑说明 #

该条件覆盖所有重叠场景：

• 缺勤时间段完全包含在指定日期范围内
• 指定日期范围完全包含在缺勤时间段内
• 缺勤时间段起始在指定范围内、结束在范围外
• 缺勤时间段结束在指定范围内、起始在范围外
• 两个时间段完全重合

完全满足需求中“绑定变量日期落在缺勤时间段内也需纳入结果”的要求。

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