Вопросы Теги

Разделение PL / SQL, разделение даты на новые даты в соответствии с закрытыми датами!

У меня есть, скажем так, "свидание" и даты вычеркнуты. 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;
5
задан Richard J. Ross III 21 June 2013 в 00:12
поделиться