PL / SQL拆分,根据黑色日期将日期分隔为新日期! [英] PL/SQL Split, separate a date into new dates according to black out dates!
问题描述
我将根据黑色日期将旅行日期分成几段。
注意:旅行日期可以在 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屋!