The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.
Thanks for the question, Tuan.
Asked: November 07, 2019 - 2:36 am UTC
Last updated: February 17, 2020 - 2:32 am UTC
Version: Oracle 12c
Viewed 1000+ times
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' );
------------------------------ | start_date | end_date | ------------------------------ | 01/11/2019 | 04/11/2019 | ------------------------------ | 07/11/2019 | 12/11/2019 | ------------------------------ | 14/11/2019 | 20/11/2019 | ------------------------------
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>
Analytic SQL got you confused? Check out Connor McDonald's complete video course.