I have a leaves_table which contains id, holiday_start, holiday_end. I have another leaves_holiday table which contains the public holiday name and it's date. now i want to in the leaves_table to add a new column and exclude the days where it is a public holiday
lets say for example
leaves_table
id. holiday_start. holiday_end
1. 09-Jul-2022. 13-Jul-2022
public holiday table
holiday_name. holiday_date
christmas 10-Jul-2022
the query should return no of days excluded as 1
id. holiday_start. holiday_end. excluded days
1 09-Jul-2022. 13-Jul-2022. 1
how do i do this?
here is the create table and insert
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');
2nd table (public holiday calendar table)
CREATE TABLE "XX_LEAVES_PUBLIC_HOLIDAYS"
( "PUBLIC_HOLIDAY_UAE_YEAR_2022" VARCHAR2(50) NOT NULL,
"HOLIDAY_DATE" DATE NOT NULL ENABLE
)
INSERT INTO XX_LEAVES_PUBLIC_HOLIDAYS (PUBLIC_HOLIDAY_UAE_YEAR_2022, HOLIDAY_DATE) VALUES (National Day,'10-Jul-2022');
CodePudding user response：
One option is to create calendar of all holiday dates (leaves_calendar CTE in my example) and then join it to public_holiday so that you'd know which dates to exclude.
Sample data:
SQL> with
2 leaves_table (id, holiday_start, holiday_end) as
3 (select 1, date '2022-07-09', date '2022-07-13' from dual union all
4 select 2, date '2022-05-25', date '2022-05-30' from dual
5 ),
6 public_holiday (holiday_name, holiday_date) as
7 (select 'Christmas' , date '2022-07-10' from dual union all
8 select 'My holiday', date '2022-07-12' from dual),
9 --
Query begins here; first create a calendar ...
10 leaves_calendar as
11 (select l.id, l.holiday_start column_value - 1 as datum
12 from leaves_table l cross join
13 table(cast(multiset(select level from dual
14 connect by level <= l.holiday_end - l.holiday_start 1
15 ) as sys.odcinumberlist))
16 )
... then return the result: start and end date, number of excluded dates and holiday names (you didn't ask for that, but ... not a problem)
17 select c.id,
18 min(c.datum) as holiday_start,
19 max(c.datum) as holiday_end,
20 sum(case when p.holiday_date = c.datum then 1 else 0 end) as excluded_days,
21 listagg(p.holiday_name, ', ') within group (order by p.holiday_date) as excluded
22 from leaves_calendar c left join public_holiday p on p.holiday_date = c.datum
23 group by c.id;
ID HOLIDAY_START HOLIDAY_END EXCLUDED_DAYS EXCLUDED
---------- --------------- --------------- ------------- ------------------------------
1 09.07.2022 13.07.2022 2 Christmas, My holiday
2 25.05.2022 30.05.2022 0
SQL>
With sample data you provided:
SQL> select * from xx_leaves_excludes;
EXCLUDE_ID HOLIDAY_START HOLIDAY_END
---------- --------------- ---------------
1 23.07.2022 20.08.2022
2 01.07.2022 02.08.2022
3 13.07.2022 29.08.2022
4 12.07.2022 01.08.2022
5 01.07.2022 29.08.2022
6 08.07.2022 08.08.2022
7 03.07.2022 20.08.2022
7 rows selected.
SQL> select * from public_holiday;
HOLIDAY_NAME HOLIDAY_DATE
--------------- ---------------
Christmas 10.07.2022
My holiday 12.07.2022
Query looks like this:
SQL> with
2 leaves_calendar as
3 (select l.exclude_id, l.holiday_start column_value - 1 as datum
4 from xx_leaves_excludesl cross join
5 table(cast(multiset(select level from dual
6 connect by level <= l.holiday_end - l.holiday_start 1
7 ) as sys.odcinumberlist))
8 )
9 select c.exclude_id,
10 min(c.datum) as holiday_start,
11 max(c.datum) as holiday_end,
12 sum(case when p.holiday_date = c.datum then 1 else 0 end) as excluded_days,
13 listagg(p.holiday_name, ', ') within group (order by p.holiday_date) as excluded
14 from leaves_calendar c left join public_holiday p on p.holiday_date = c.datum
15 group by c.exclude_id;
EXCLUDE_ID HOLIDAY_START HOLIDAY_END EXCLUDED_DAYS EXCLUDED
---------- --------------- --------------- ------------- ----------------------------------------
1 23.07.2022 20.08.2022 0
2 01.07.2022 02.08.2022 2 Christmas, My holiday
3 13.07.2022 29.08.2022 0
4 12.07.2022 01.08.2022 1 My holiday
5 01.07.2022 29.08.2022 2 Christmas, My holiday
6 08.07.2022 08.08.2022 2 Christmas, My holiday
7 03.07.2022 20.08.2022 2 Christmas, My holiday
7 rows selected.
SQL>
CodePudding user response：
compare leave date rage with hodiday and get count as excluded_days
select l.id, l.holiday_start, l.holiday_end,
(select Count(1) from leaves_holiday lh
where l.holiday_start<= lh.holiday_date and l.holiday_end >= lh.holiday_date) as excluded_days
from leaves_table l