Hi All,
I have a question regarding how to generate records between two date ranges. The records between the two date ranges (start_dt and end_dt) should exclude the weekends and holidays (this is stored in a separate holiday table).
Below is the test data setup:
create table tbl_data
(
id_group varchar2(10),
start_dt date,
end_dt date,
qty_day number
);
create table tbl_holiday
(
id_group varchar2(10),
holiday date
);
insert into tbl_data
values('id_1',to_date('06/02/2016','MM/DD/YYYY'),to_date('06/08/2016','MM/DD/YYYY'),5);
insert into tbl_data
values('id_1',to_date('06/06/2016','MM/DD/YYYY'),to_date('06/16/2016','MM/DD/YYYY'),8);
insert into tbl_data
values('id_2',to_date('06/02/2016','MM/DD/YYYY'),to_date('06/10/2016','MM/DD/YYYY'),7);
insert into tbl_data
values('id_2',to_date('06/08/2016','MM/DD/YYYY'),to_date('06/15/2016','MM/DD/YYYY'),12);
insert into tbl_holiday
values('id_1',to_date('06/07/2016','MM/DD/YYYY'));
insert into tbl_holiday
values('id_2',to_date('06/09/2016','MM/DD/YYYY'));
commit;
For the above sample data, for the first row in the tbl_data, below are the records I would expect;
id_group business_date qty_day
id_1 06/02/2016 5
id_1 06/03/2016 5
id_1 06/06/2016 5
id_1 06/08/2016 5
Similarly, for the third row in the tbl_data, I would expect the below output:
id_group business_date qty_day
id_2 06/02/2016 7
id_2 06/03/2016 7
id_2 06/06/2016 7
id_2 06/07/2016 7
id_2 06/08/2016 7
id_2 06/10/2016 7
Could someone please help me out on this.
Thank you.