Oracle中如何通过绑定变量统计请假期间指定排除假期天数

假设2022年8月4日为公共假期，现有存储假期起止日期的XX_LEAVES_EXCLUDES表。需要通过绑定变量:LS（请假开始日期）、:LE（请假结束日期），统计该日期在请假起止范围内的排除天数。示例：请假时间为2022年8月1日至2022年8月10日时，排除天数为1。

我已尝试以下SQL语句：

SELECT :LS "Leave Start Date",
       :LE "Leave End Date",
        0 "Excluded Days"
FROM   Dual

以下为参考表结构、序列、触发器及插入数据的代码：

create table XX_LEAVES_EXCLUDES
(
exclude_id number not null primary key,
holiday_start date not null,
holiday_end date not null
);


create sequence seq_exclude_id MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 2;

create or replace trigger trg_exclude_id
before insert 
on XX_LEAVES_EXCLUDES
for each row
begin
:new.exclude_id:=seq_exclude_id.nextval;
end;



INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('23-Jul-2022','20-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('01-Jul-2022','02-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('13-Jul-2022','29-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('12-Jul-2022','01-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('01-Jul-2022','29-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('08-Jul-2022','08-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('03-Jul-2022','20-Aug-2022');

解决方案 #

要实现需求，核心是判断目标公共假期是否同时落在请假时间段和假期表的区间内，以下是可行的SQL：

固定公共假期版本

SELECT :LS "Leave Start Date",
       :LE "Leave End Date",
       CASE 
           WHEN DATE '2022-08-04' BETWEEN :LS AND :LE
                AND EXISTS (
                    SELECT 1 
                    FROM XX_LEAVES_EXCLUDES
                    WHERE DATE '2022-08-04' BETWEEN holiday_start AND holiday_end
                )
           THEN 1
           ELSE 0
       END "Excluded Days"
FROM Dual;

动态公共假期版本（支持绑定变量指定假期）

SELECT :LS "Leave Start Date",
       :LE "Leave End Date",
       :PUBLIC_HOLIDAY "Public Holiday",
       CASE 
           WHEN :PUBLIC_HOLIDAY BETWEEN :LS AND :LE
                AND EXISTS (
                    SELECT 1 
                    FROM XX_LEAVES_EXCLUDES
                    WHERE :PUBLIC_HOLIDAY BETWEEN holiday_start AND holiday_end
                )
           THEN 1
           ELSE 0
       END "Excluded Days"
FROM Dual;

说明 #

• 通过CASE分支判断公共假期是否满足两个条件：在请假起止日期范围内、存在于XX_LEAVES_EXCLUDES的假期区间中
• EXISTS子查询确保该日期确实是已登记的公共假期
• 代入示例中的请假范围（2022-08-01至2022-08-10）时，2022-08-04符合所有条件，返回排除天数1

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