subtract certain date from two dates

Let’s say it is announced that 4 Aug 2022 is a public holiday. I have a table which shows the holiday_start and holiday_end dates. How do I exclude this date “4 Aug 2022” by inputting any date i want (bind variable) and showing it as excluded day

eg. leave is from 01-Aug-2022 -> 10-Aug-2022 excluded days= 1

i’ve tried this:

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

and here is the table for reference

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

I dont understand very well but assuming that “04 Aug 2022” has inserted in XX_LEAVES_EXCLUDES we have:

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

Please me back if it’s correct or specify me better the problem.

Thank you