PL/SQL Split, separate a date into new dates according to black out dates!

Question

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;

It is to represent infinity, not to a specific date. Ex: Start Date: 00010101 End Date:99991231. The performance should be fast. — ahmet, Mar 10 '11 at 10:09

score 7 · Answer 1 · edited May 23 '17 at 12:22

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

Thanks. @Jeffrey-Kemp. But what happens if our date is between Start Date:`00010101` End Date:`99991231`. We have to change the `CONNECT BY LEVEL <= 1000` to bigger number and the **performance** will go down. — ahmet, Mar 10 '11 at 15:30
@ahment performance will go down for anything calculating 10000 years, but first I don't think this is your case, and second, you can easily optimize it by generating the days sub-query only for the next day after the end of the first block out and the previous day to the beginning of the last block out. Then, generate two new groups for 0001 01 01 to first blockout - 1 and last blockout + 1 to 9999 12 31 (if required). You can do it even in the same sentence with unions. — jachguate, Mar 11 '11 at 00:00
@ahmet, try it and see, you'll probably find it performs quite well regardless. jachguate's suggestion is also good to minimise the number of generated rows. You could get the MIN and MAX dates from the traveldate and blackout tables and generate just the minimum number of rows required. — Jeffrey Kemp, Mar 11 '11 at 01:55
Your code results: Passed all testes but the problem is: It is very slow. And when it comes to increasing the level isn't the solution. — ahmet, Mar 13 '11 at 17:13
The example code I posted runs in under 1 second on my machine :) If you want some help getting it to perform better, please post your version of the query, the explain plan, and the max range of dates being used (i.e. earliest and latest dates from the two tables). — Jeffrey Kemp, Mar 14 '11 at 00:14

Rob van Wijk · Accepted Answer · 2011-03-14T10:31:49.223

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.

But changing the travel date to 04/01/2001 - 11/12/2013 should give result as
`**04/01/2001 31/10/2010** 16/02/2011 12/03/2011 30/06/2011 **11/12/2013**`
which doesn't generate: `02/01/2001-31/10/2010` — ahmet, Mar 10 '11 at 16:25
@ahmet: I just checked it and my query returns exactly the three rows with the new input... — Rob van Wijk, Mar 11 '11 at 09:07
Here are the test results: Test 1: `01/01/0001-09.02.2011` isn't listed. Test 2: `01.01.2011-01.02.2011` `07.05.2011-06.07.2011` aren't listed. Test 3: `21.06.2011-07.08.2011` is wrong. Test 4: `26.04.2011-09.05.2011` is wrong. Test 5: `13.05.2011-17.05.2011` should not be included. Test 6: All correct. — ahmet, Mar 13 '11 at 16:39
You are right. I added a new section to my answer with an improved query. — Rob van Wijk, Mar 14 '11 at 10:33

PL/SQL Split, separate a date into new dates according to black out dates!

2 Answers2

Linked