Hi Tom,
I need to generate Dates between two date ranges and split into segments from Holidays.
Here is my tables and data:
DROP TABLE WORKING_DAY CASCADE CONSTRAINTS;
DROP TABLE HOLIDAY_BREAK CASCADE CONSTRAINTS;
CREATE TABLE WORKING_DAY
(
START_DATE DATE,
END_DATE DATE
);
CREATE TABLE HOLIDAY_BREAK
(
START_DATE DATE,
END_DATE DATE
);
INSERT INTO WORKING_DAY(START_DATE, END_DATE ) VALUES (DATE '2019-11-01', DATE '2019-11-20' );
INSERT INTO HOLIDAY_BREAK(START_DATE, END_DATE ) VALUES (DATE '2019-11-05', DATE '2019-11-06' );
INSERT INTO HOLIDAY_BREAK(START_DATE, END_DATE ) VALUES (DATE '2019-11-13', DATE '2019-11-13' );
Excepted Result:
------------------------------
| start_date | end_date |
------------------------------
| 01/11/2019 | 04/11/2019 |
------------------------------
| 07/11/2019 | 12/11/2019 |
------------------------------
| 14/11/2019 | 20/11/2019 |
------------------------------
Thanks in advance.
TuấnBC
SQL> CREATE TABLE WORKING_DAY
2 (
3 START_DATE DATE,
4 END_DATE DATE
5 );
Table created.
SQL>
SQL> CREATE TABLE HOLIDAY_BREAK
2 (
3 START_DATE DATE,
4 END_DATE DATE
5 );
Table created.
SQL>
SQL> INSERT INTO WORKING_DAY(START_DATE, END_DATE ) VALUES (DATE '2019-11-01', DATE '2019-11-20' );
1 row created.
SQL>
SQL> INSERT INTO HOLIDAY_BREAK(START_DATE, END_DATE ) VALUES (DATE '2019-11-05', DATE '2019-11-06' );
1 row created.
SQL> INSERT INTO HOLIDAY_BREAK(START_DATE, END_DATE ) VALUES (DATE '2019-11-13', DATE '2019-11-13' );
1 row created.
--
-- get all the relevant dates in time order
--
SQL>
SQL> with t as
2 (
3 select start_date from working_day
4 union all
5 select end_date from working_day
6 union all
7 select start_date-1 from HOLIDAY_BREAK
8 union all
9 select end_date+1 from HOLIDAY_BREAK
10 )
11 select * from t
12 order by 1;
START_DAT
---------
01-NOV-19
04-NOV-19
07-NOV-19
12-NOV-19
14-NOV-19
20-NOV-19
6 rows selected.
--
-- assign them to pairs of dates
--
SQL>
SQL>
SQL> with t as
2 (
3 select start_date from working_day
4 union all
5 select end_date from working_day
6 union all
7 select start_date-1 from HOLIDAY_BREAK
8 union all
9 select end_date+1 from HOLIDAY_BREAK
10 )
11 select
12 trunc((row_number() over ( order by start_date ) - 1 )/2) as pair,
13 t.* from t
14 order by 1;
PAIR START_DAT
---------- ---------
0 01-NOV-19
0 04-NOV-19
1 07-NOV-19
1 12-NOV-19
2 14-NOV-19
2 20-NOV-19
6 rows selected.
--
-- then group by to get the first and last for each pair
--
SQL>
SQL> with t as
2 (
3 select start_date from working_day
4 union all
5 select end_date from working_day
6 union all
7 select start_date-1 from HOLIDAY_BREAK
8 union all
9 select end_date+1 from HOLIDAY_BREAK
10 ), t1 as
11 (
12 select
13 trunc((row_number() over ( order by start_date ) - 1 )/2) as pair,
14 t.* from t
15 )
16 select min(start_date), max(start_date)
17 from t1
18 group by pair
19 order by pair;
MIN(START MAX(START
--------- ---------
01-NOV-19 04-NOV-19
07-NOV-19 12-NOV-19
14-NOV-19 20-NOV-19
3 rows selected.
SQL>