PL / SQL拆分，根据黑色日期将日期分隔为新日期！ [英] PL/SQL Split, separate a date into new dates according to black out dates!
本文介绍了PL / SQL拆分，根据黑色日期将日期分隔为新日期！的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我将根据黑色日期将旅行日期分成几段。
注意：旅行日期可以在 0 - 9999之间99 99
示例：
旅行日期：
旅行|开始日期|结束日期
T | 2011 01 04 | 2011 12 11
黑色日期：
BO |开始日期|结束日期
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
异常结果：
新旅行|开始日期|结束日期
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
目视：
NAME：date range
旅行日期：----- [------------------------- - ] -
A： - [------] -------------------------
B：------ [---] ------------------------
C：---- ---------- [---] ----------------
D：------------- --- [------] -----------
结果：
X1：----------- [ - ] ------ --------------
X2：----------------------- [------ - ] -
示例2： p>
旅行日期： - [----------------------- ---------] -
BO日期A：---- [------] -------------- -----------
BO日期B：---------------------- [------] - ------
BO日期C：-------------------- [---] ----------- -
BO日期D：------------------ [------] -----------
结果X1： - [ - ] -------------------------- ------
结果X2：----------- [------] ------------------
结果X3：----------------------------- [----] -
示例3：
旅行日期：] ----------------------------------- [
BO日期A：---- [------] -------------------------
BO日期B：--- ---------------------- [---] -------
BO日期C：-------- -------- [---] ----------------
BO日期D：------------- ----- [------] -----------
结果X1：---] ----------- ----------------------
结果X2：----------- [ - ] ------ ----------------
结果X3：--------------------------- - [-------
我如何使用PL SQL？ p>
谢谢。
以下是表和测试用例：
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
）;
*
测试案例1
-------------------------------------- -----------------------------
预期成果：
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
目视：
旅行：
------------------------ ----------------------------
BO：
- [ - ] - --------------------------------------------
- ------------- [------] -----------------------------
----------------------------- [---------------] ------
结果：
[ - ] --- [--------] ------- [-----] ---------------- [-----]
* /
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
-------------------------------------- -----------------------------
预期成果：
01/01/2011 01/02/2011
07/05/2011 06/07/2011
21/07/2011 31/12/2011
目视：
旅行：
[ - -------------------------------------------------- ---]
BO：
- [----------------------] ------ ------------------------
--------------- [ - ] --- ----------------------------------
------------ --------------------- [--------] -------------
- -------------------------------------- [--------] - ----
结果：
[ - ] --------------------- [------ ---] -------------- [----]
* /
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
-------------------------------------- -----------------------------
预期成果：
04/05/2011 03/06/2011
21/06/2011 07/08/2011
目视：
旅行：
[--------------- ---------------------------------------]
BO：
- [----------------------] -------------------- ----------
--------------- [ - ] ----------------- --------------------
-------------------------- ------- [--------] -------------
---------------- ------------------------ [--------] ------
结果：
[ - ] --------------------- [---------] ---------- ---- [----]
* /
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
*
测试案例4
-------------------------------------- -----------------------------
预期成果：
21/02/2011 09/04/2011
26/04/2011 09/05/2011
目视：
旅行：
---- [----------- --------------] -------------------------
BO：
- [----] -------------------------------------- ----------
---- [----] -------------------------- --------------------
------------- [--------] --- ------------------------------
---------------- - [ - ] ----------------------------------
------ -------------------- [--------] --------------------
结果：
---------- [ - ] -------- [ - ] ---------- -------------------
* /
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;
*
测试案例5
-------------------------------------- -----------------------------
预期成果：
21/02/2011 04/05/2011
目视：
旅行：
------ [----------------------- - ] -----------------------
BO：
- [-----] - ----------------------------------------------
- [ - ] -------------------------------------------- ------
---------------------------- [--------] - ----------------
------------------------------ [-----] -------------------
-------------------- ----------- [ - ] ---------------------
结果：
-------- [-------------------] --------------------- ------
* /
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;
*
测试案例6
-------------------------------------- -----------------------------
预期结果：
否结果
目视：
旅行：
------ [----------------------------] - ------------------
BO：
- [--------------- ------------------------] -------------
结果：
否结果
* /
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;
解决方案
您的表：
SQL>创建表格旅行（start_date，end_date）
2 as
3选择日期2011-01-04，日期2011-12-11从双
4 /
创建表。
SQL>创建表black_out_dates（bo，start_date，end_date）
2 as
3选择'A'，date'2010-11-01'，date'2011-02-11'from dual union all
4选择'B'，日期'2011-01-20'，日期'2011-02-15'从双联合全部
5选择'C'，日期'2011-03-13'，日期2011 -04-10'from dual union all
6选择'D'，date'2011-03-20'，date'2011-06-29'from dual
7 /
创建表。
这个查询考虑到完全重叠的黑色时段：
SQL>选择'X'|| to_char（row_number（）over（order by new_start_date））new_travel
2，new_start_date
3，new_end_date
从（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从（select bo.start_date
12，bo.end_date
13，t.end_date t_end_date
14从black_out_dates bo
15，旅行t
16其中t.start_date< = bo.end_date
17和t.end_date> = bo.start_date
18联合全部
19选择start_date - 1
20，start_date - 1
21，null
22从旅行
23）
24）
25其中rn_start_date <= rn_end_date
26）
27其中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行。
如果您的black_out_dates表包含N行，那么最多N + 1个空格。该查询在[2011-01-03,2011-01-03]中组成一个虚拟黑屏日期，然后使用分析函数LEAD来确定下一个截止日期开始的位置。完全重叠的时间段由ROW_NUMBER个分析功能删除，因为它们会导致间隔时间的变化。
编辑14 -3-2011
使用这些表：
SQL>创建表格旅行（start_date，end_date）
2 as
3选择日期'2001-01-04'，日期'2013-12-11'从双
4 /
创建表。
SQL>创建表black_out_dates（bo，start_date，end_date）
2 as
3选择'A'，date'2010-11-01'，date'2011-02-11'from dual union all
4选择'B'，日期'2011-01-20'，日期'2011-02-15'从双联合全部
5选择'C'，日期'2011-03-13'，日期2011 -04-10'from dual union all
6选择'D'，date'2011-03-20'，date'2011-06-29'from dual
7 /
创建表。
我以前的查询仍然没有正确处理重叠的时间段。所以这里是一个修订版本，方便地存储在视图v：
SQL>创建视图v
2为
3，t1为
4（选择bo.start_date
5，bo.end_date
6，t.end_date t_end_date
7从black_out_dates bo
8，旅行t
9其中bo.start_date< = t.end_date
10和bo.end_date> = t.start_date
11 union all
12选择start_date - 1
13，start_date - 1
14，end_date
15从旅行
16）
17，t2 as
18 （选择t1。*
19，nvl
20（max（end_date）
21超过
22（order by start_date，end_date desc
23行在无界前面和1前面的
24）
25，to_date（'1'，'j'）
26）max_date
27 from t1
28）
29， t3为
30（select start_date
31，end_date
32，t_end_date
33，sum（case w母鸡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 /
查看创建。
测试结果：
SQL>设置反馈
SQL>备注测试1
SQL>选择*从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> comment Test 2
SQL>删除旅行
2 /
SQL>删除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（B，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>选择*从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>备注测试3
SQL>删除旅行
2 /
SQL>删除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>选择*从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>备注测试4
SQL>删除旅行
2 /
SQL>删除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>选择*从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>备注测试5
SQL>删除旅行
2 /
SQL>删除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>选择*从v
2 /
NEW_START_DATE NEW_END_DATE
------------------- ------- ------------
21-02-2011 00:00:00 04-05-2011 00:00:00
SQL>备注Test 6
SQL>删除旅行
2 /
SQL>删除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>选择*从v
2 /
问候，
Rob。
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;
解决方案
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.
这篇关于PL / SQL拆分，根据黑色日期将日期分隔为新日期！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
