从两个日期减去某一日期

Question

假设宣布2022年8月4日为公共假日。我有一张显示holiday_start和holiday_end日期的表格。如何通过输入任何我想要的日期(绑定变量)并将其显示为排除日，从而排除此日期“8月4日2022年”

例如：休假日期为01-8月-2022 -> 10-8月-2022不包括days= 1

我试过这个：

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');

Answer 1

我不太明白，但假设“04年8月2022年8月4日”插入了XX_LEAVES_EXCLUDES，我们有：

WITH XX_LEAVES_EXCLUDES AS (
                            select 1 as exclude_id, '04/08/2022' as holiday_start, '04/08/2022' as holiday_end from dual 
                           )
                    select 
                           :LS      as "Leave Start Date",
                           :LE      as "Leave End Date",                    
                           count(*) as "Excluded Days" 
                      from (
                            select to_date(:LS,'DD/MM/YYYY') + rownum -1 as day_by_day
                              from all_objects, XX_LEAVES_EXCLUDES X
                             where rownum <= to_date(:LE,'DD/MM/YYYY') - to_date(:LS,'DD/MM/YYYY') + 1
                           ) A,
                           XX_LEAVES_EXCLUDES B
                     where A.day_by_day between B.holiday_start AND B.holiday_end;
                     
Leave Start Date   Leave End Date    Excluded Days
------------------------------------------------------
01/08/2022         10/08/2022        1

如果是正确的，请还给我，或者更好地说明问题。

谢谢

Answer 2

如果XX_LEAVES_EXCUDES是有叶子的列表：

WITH XX_LEAVES_EXCLUDES AS 
(
  SELECT 1 AS exclude_id, TO_DATE('23-Jul-2022','DD-Mon-YYYY') as Start_date, TO_DATE('20-Aug-2022','DD-Mon-YYYY') as End_date FROM dual UNION ALL
  SELECT 2, TO_DATE('01-Jul-2022','DD-Mon-YYYY') as Start_date, TO_DATE('02-Aug-2022','DD-Mon-YYYY') as End_date FROM dual UNION ALL
  SELECT 3, TO_DATE('13-Jul-2022','DD-Mon-YYYY') as Start_date, TO_DATE('29-Aug-2022','DD-Mon-YYYY') as End_date FROM dual UNION ALL
  SELECT 4, TO_DATE('12-Jul-2022','DD-Mon-YYYY') as Start_date, TO_DATE('01-Aug-2022','DD-Mon-YYYY') as End_date FROM dual UNION ALL
  SELECT 5, TO_DATE('01-Jul-2022','DD-Mon-YYYY') as Start_date, TO_DATE('29-Aug-2022','DD-Mon-YYYY') as End_date FROM dual UNION ALL
  SELECT 6, TO_DATE('08-Jul-2022','DD-Mon-YYYY') as Start_date, TO_DATE('08-Aug-2022','DD-Mon-YYYY') as End_date FROM dual UNION ALL
  SELECT 7, TO_DATE('03-Jul-2022','DD-Mon-YYYY') as Start_date, TO_DATE('20-Aug-2022','DD-Mon-YYYY') as End_date FROM dual
),
HOLIDAYS AS
(
  SELECT TO_DATE('04-Aug-2022','DD-Mon-YYYY') AS excluded FROM dual
)
SELECT leaves.Start_date, leaves.End_date, NVL(excludes.excluded_days,0)
  FROM XX_LEAVES_EXCLUDES leaves
       LEFT OUTER JOIN (SELECT lvs.exclude_id, COUNT(hday.excluded) AS excluded_days
                          FROM XX_LEAVES_EXCLUDES lvs
                             , HOLIDAYS           hday
                         WHERE 1 = 1
                           AND hday.excluded BETWEEN lvs.Start_date AND lvs.End_date
                         GROUP BY lvs.exclude_id) excludes
                    ON leaves.exclude_id = excludes.exclude_id
 ORDER BY leaves.exclude_id;