Menu
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0 - 9999 99 99
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
NAME : date range
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : ----------------------[------]-------
BO Date C : --------------------[---]------------
BO Date D : ------------------[------]-----------
Result X1 : -[--]--------------------------------
Result X2 : -----------[------]------------------
Result X3 : -----------------------------[----]--
Sample 3:
Travel Date : ]-----------------------------------[
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : ---]---------------------------------
Result X2 : -----------[--]----------------------
Result X3 : -----------------------------[-------
How can I do it using PL SQL ?
Thanks.
Here are the tables and test cases:
DROP TABLE TRACES.TRAVEL CASCADE CONSTRAINTS;
CREATE TABLE TRACES.TRAVEL
(
START_DATE DATE,
END_DATE DATE
);
DROP TABLE TRACES.BLACK_OUT_DATES CASCADE CONSTRAINTS;
CREATE TABLE TRACES.BLACK_OUT_DATES
(
BO CHAR( 1 BYTE ),
START_DATE DATE,
END_DATE DATE
);
/*
TEST CASE 1
-------------------------------------------------------------------
Expected Results:
01/01/0001 09/02/2011
16/02/2011 01/04/2011
21/04/2011 10/05/2011
16/06/2011 19/11/2011
30/11/2011 31/12/9999
Visually:
Travel:
----------------------------------------------------
BO:
--[--]----------------------------------------------
---------------[------]-----------------------------
-----------------------------[---------------]------
Result:
[-]---[--------]-------[-----]----------------[-----]
*/
TRUNCATE TABLE TRACES.TRAVEL;
TRUNCATE TABLE TRACES.BLACK_OUT_DATES;
INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '0001-01-01', DATE '9999-12-31' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-10', DATE '2011-02-15' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-04-02', DATE '2011-04-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-05-11', DATE '2011-06-15' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-11-20', DATE '2011-11-29' );
--INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-09', DATE '2011-05-12' );
COMMIT;
SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;
/*
TEST CASE 2
-------------------------------------------------------------------
Expected Results:
01/01/2011 01/02/2011
07/05/2011 06/07/2011
21/07/2011 31/12/2011
Visually:
Travel:
[------------------------------------------------------]
BO:
--[----------------------]------------------------------
---------------[--]-------------------------------------
---------------------------------[--------]-------------
----------------------------------------[--------]------
Result:
[--]---------------------[---------]--------------[----]
*/
TRUNCATE TABLE TRACES.TRAVEL;
TRUNCATE TABLE TRACES.BLACK_OUT_DATES;
INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-01-01', DATE '2011-12-31' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-05-06' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-03-03', DATE '2011-03-05' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-07-07', DATE '2011-07-09' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-07-08', DATE '2011-07-20' );
COMMIT;
SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;
/*
TEST CASE 3
-------------------------------------------------------------------
Expected Results:
04/05/2011 03/06/2011
21/06/2011 07/08/2011
Visually:
Travel:
[------------------------------------------------------]
BO:
--[----------------------]------------------------------
---------------[--]-------------------------------------
---------------------------------[--------]-------------
----------------------------------------[--------]------
Result:
[--]---------------------[---------]--------------[----]
*/
TRUNCATE TABLE TRACES.TRAVEL;
TRUNCATE TABLE TRACES.BLACK_OUT_DATES;
INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-04-02', DATE '2011-10-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-01-01', DATE '2011-05-03' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-06-04', DATE '2011-06-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-06-06', DATE '2011-06-08' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-08-08', DATE '2011-12-30' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-08-08', DATE '2011-12-30' );
COMMIT;
SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;
/*
TEST CASE 4
-------------------------------------------------------------------
Expected Results:
21/02/2011 09/04/2011
26/04/2011 09/05/2011
Visually:
Travel:
----[-------------------------]-------------------------
BO:
--[----]------------------------------------------------
----[----]----------------------------------------------
-------------[--------]---------------------------------
------------------[--]----------------------------------
--------------------------[--------]--------------------
Result:
----------[--]--------[--]-----------------------------
*/
TRUNCATE TABLE TRACES.TRAVEL;
TRUNCATE TABLE TRACES.BLACK_OUT_DATES;
INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-05-15' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-02-15' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-10', DATE '2011-02-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-04-10', DATE '2011-04-25' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-04-15', DATE '2011-04-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-10', DATE '2011-05-20' );
COMMIT;
SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;
/*
TEST CASE 5
-------------------------------------------------------------------
Expected Results:
21/02/2011 04/05/2011
Visually:
Travel:
------[-------------------------]-----------------------
BO:
-[-----]------------------------------------------------
--[--]--------------------------------------------------
----------------------------[--------]------------------
------------------------------[-----]-------------------
-------------------------------[--]---------------------
Result:
--------[-------------------]---------------------------
*/
TRUNCATE TABLE TRACES.TRAVEL;
TRUNCATE TABLE TRACES.BLACK_OUT_DATES;
INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-05-17' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-05', DATE '2011-02-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-07', DATE '2011-02-09' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-05-05', DATE '2011-05-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-05-07', DATE '2011-05-15' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-09', DATE '2011-05-12' );
COMMIT;
SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;
/*
TEST CASE 6
-------------------------------------------------------------------
Expected Results:
No Result
Visually:
Travel:
------[----------------------------]--------------------
BO:
--[---------------------------------------]-------------
Result:
No Result
*/
TRUNCATE TABLE TRACES.TRAVEL;
TRUNCATE TABLE TRACES.BLACK_OUT_DATES;
INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-09-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-01-05', DATE '2011-10-10' );
COMMIT;
SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;
545
This returns a discrete list of all dates that exist in the Travel Dates range but not in the Blackout Dates list, and then combines them using Oracle - Convert value from rows into ranges:
WITH traveldate AS
(SELECT TO_DATE('2011 01 04','YYYY MM DD') AS start_date
,TO_DATE('2011 12 11','YYYY MM DD') AS end_date FROM DUAL)
,blackout AS
(SELECT TO_DATE('2010 11 01','YYYY MM DD') AS start_date
,TO_DATE('2011 02 11','YYYY MM DD') AS end_date FROM DUAL
UNION ALL
SELECT TO_DATE('2011 01 20','YYYY MM DD') AS start_date
,TO_DATE('2011 02 15','YYYY MM DD') AS end_date FROM DUAL
UNION ALL
SELECT TO_DATE('2011 03 13','YYYY MM DD') AS start_date
,TO_DATE('2011 04 10','YYYY MM DD') AS end_date FROM DUAL
UNION ALL
SELECT TO_DATE('2011 03 20','YYYY MM DD') AS start_date
,TO_DATE('2011 06 29','YYYY MM DD') AS end_date FROM DUAL)
,days AS
(SELECT TO_DATE('2010 01 01','YYYY MM DD') + ROWNUM d
FROM DUAL CONNECT BY LEVEL <= 1000)
,base AS
(SELECT d AS n
FROM days, traveldate
WHERE d >= traveldate.start_date AND d <= traveldate.end_date
MINUS
SELECT d AS n
FROM days, blackout
WHERE d >= blackout.start_date AND d <= blackout.end_date
)
,lagged AS
(
SELECT n, LAG(n) OVER (ORDER BY n) lag_n FROM base
)
, groups AS
(
SELECT n, row_number() OVER (ORDER BY n) groupnum
FROM lagged
WHERE lag_n IS NULL OR lag_n < n-1
)
, grouped AS
(
SELECT n, (SELECT MAX(groupnum) FROM groups
WHERE groups.n <= base.n
) groupnum
FROM base
)
SELECT groupnum, MIN(n), MAX(n)
FROM grouped
GROUP BY groupnum
ORDER BY groupnum;
Result:
GROUPNUM MIN(N) MAX(N)
1 16/02/2011 12/03/2011
2 30/06/2011 11/12/2011
Your tables:
SQL> create table travel (start_date,end_date)
2 as
3 select date '2011-01-04', date '2011-12-11' from dual
4 /
Table created.
SQL> create table black_out_dates (bo,start_date,end_date)
2 as
3 select 'A', date '2010-11-01', date '2011-02-11' from dual union all
4 select 'B', date '2011-01-20', date '2011-02-15' from dual union all
5 select 'C', date '2011-03-13', date '2011-04-10' from dual union all
6 select 'D', date '2011-03-20', date '2011-06-29' from dual
7 /
Table created.
And the query, which takes into account completely overlapping black out periods:
SQL> select 'X' || to_char(row_number() over (order by new_start_date)) new_travel
2 , new_start_date
3 , new_end_date
4 from ( select end_date + 1 new_start_date
5 , lead(start_date - 1, 1, t_end_date) over (order by start_date) new_end_date
6 from ( select start_date
7 , end_date
8 , t_end_date
9 , row_number() over (order by start_date) rn_start_date
10 , row_number() over (order by end_date) rn_end_date
11 from ( select bo.start_date
12 , bo.end_date
13 , t.end_date t_end_date
14 from black_out_dates bo
15 , travel t
16 where t.start_date <= bo.end_date
17 and t.end_date >= bo.start_date
18 union all
19 select start_date - 1
20 , start_date - 1
21 , null
22 from travel
23 )
24 )
25 where rn_start_date <= rn_end_date
26 )
27 where new_start_date <= new_end_date
28 order by new_start_date
29 /
NEW_TRAVEL NEW_START_DATE NEW_END_DATE
---------- ------------------- -------------------
X1 16-02-2011 00:00:00 12-03-2011 00:00:00
X2 30-06-2011 00:00:00 11-12-2011 00:00:00
2 rows selected.
If your black_out_dates table contains N rows, then there at most N+1 gaps. The query makes up one dummy black out date period at [2011-01-03,2011-01-03], and then uses the analytic function LEAD to determine where the next black out date starts. Completely overlapping periods are removed by the ROW_NUMBER analytic functions, because they mess up the gap periods.
EDIT 14-3-2011
With these tables:
SQL> create table travel (start_date,end_date)
2 as
3 select date '2001-01-04', date '2013-12-11' from dual
4 /
Table created.
SQL> create table black_out_dates (bo,start_date,end_date)
2 as
3 select 'A', date '2010-11-01', date '2011-02-11' from dual union all
4 select 'B', date '2011-01-20', date '2011-02-15' from dual union all
5 select 'C', date '2011-03-13', date '2011-04-10' from dual union all
6 select 'D', date '2011-03-20', date '2011-06-29' from dual
7 /
Table created.
My previous query still did not handle overlapping periods correctly. So here is a revised version, conveniently stored in view v:
SQL> create view v
2 as
3 with t1 as
4 ( select bo.start_date
5 , bo.end_date
6 , t.end_date t_end_date
7 from black_out_dates bo
8 , travel t
9 where bo.start_date <= t.end_date
10 and bo.end_date >= t.start_date
11 union all
12 select start_date - 1
13 , start_date - 1
14 , end_date
15 from travel
16 )
17 , t2 as
18 ( select t1.*
19 , nvl
20 ( max(end_date)
21 over
22 ( order by start_date,end_date desc
23 rows between unbounded preceding and 1 preceding
24 )
25 , to_date('1','j')
26 ) max_date
27 from t1
28 )
29 , t3 as
30 ( select start_date
31 , end_date
32 , t_end_date
33 , sum( case when start_date > max_date then 1 else 0 end )
34 over
35 ( order by start_date, end_date desc ) grp
36 from t2
37 )
38 , t4 as
39 ( select max(end_date) + 1 new_start_date
40 , lead(min(start_date) - 1, 1, t_end_date) over (order by min(start_date)) new_end_date
41 from t3
42 group by t_end_date
43 , grp
44 )
45 select new_start_date
46 , new_end_date
47 from t4
48 where new_start_date <= new_end_date
49 /
View created.
And the test results:
SQL> set feedback off
SQL> remark Test 1
SQL> select * from v
2 /
NEW_START_DATE NEW_END_DATE
------------------- -------------------
04-01-2001 00:00:00 31-10-2010 00:00:00
16-02-2011 00:00:00 12-03-2011 00:00:00
30-06-2011 00:00:00 11-12-2013 00:00:00
SQL> remark Test 2
SQL> delete travel
2 /
SQL> delete black_out_dates
2 /
SQL> INSERT INTO TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-01-01', DATE '2011-12-31' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-05-06' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-03-03', DATE '2011-03-05' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-07-07', DATE '2011-07-09' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-07-08', DATE '2011-07-20' );
SQL> select * from v
2 /
NEW_START_DATE NEW_END_DATE
------------------- -------------------
01-01-2011 00:00:00 01-02-2011 00:00:00
07-05-2011 00:00:00 06-07-2011 00:00:00
21-07-2011 00:00:00 31-12-2011 00:00:00
SQL> remark Test 3
SQL> delete travel
2 /
SQL> delete black_out_dates
2 /
SQL> INSERT INTO TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-04-02', DATE '2011-10-20' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-01-01', DATE '2011-05-03' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-06-04', DATE '2011-06-20' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-06-06', DATE '2011-06-08' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-08-08', DATE '2011-12-30' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-08-08', DATE '2011-12-30' );
SQL> select * from v
2 /
NEW_START_DATE NEW_END_DATE
------------------- -------------------
04-05-2011 00:00:00 03-06-2011 00:00:00
21-06-2011 00:00:00 07-08-2011 00:00:00
SQL> remark Test 4
SQL> delete travel
2 /
SQL> delete black_out_dates
2 /
SQL> INSERT INTO TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-05-15' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-02-15' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-10', DATE '2011-02-20' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-04-10', DATE '2011-04-25' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-04-15', DATE '2011-04-20' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-10', DATE '2011-05-20' );
SQL> select * from v
2 /
NEW_START_DATE NEW_END_DATE
------------------- -------------------
21-02-2011 00:00:00 09-04-2011 00:00:00
26-04-2011 00:00:00 09-05-2011 00:00:00
SQL> remark Test 5
SQL> delete travel
2 /
SQL> delete black_out_dates
2 /
SQL> INSERT INTO TRAVEL VALUES ( DATE '2011-02-10', DATE '2011-05-17' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-05', DATE '2011-02-20' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-07', DATE '2011-02-09' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-05-05', DATE '2011-05-20' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-05-07', DATE '2011-05-15' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-09', DATE '2011-05-12' );
SQL> select * from v
2 /
NEW_START_DATE NEW_END_DATE
------------------- -------------------
21-02-2011 00:00:00 04-05-2011 00:00:00
SQL> remark Test 6
SQL> delete travel
2 /
SQL> delete black_out_dates
2 /
SQL> INSERT INTO TRAVEL VALUES (DATE '2011-02-10', DATE '2011-09-20' );
SQL> INSERT INTO BLACK_OUT_DATES VALUES ('A', DATE '2011-01-05', DATE '2011-10-10' );
SQL> select * from v
2 /
Regards,
Rob.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
