regexp_substr query into hacer formato fecha oracle date plsql split

query - split oracle pl sql



PL/SQL Split, separa una fecha en nuevas fechas de acuerdo a las fechas de black out! (2)

Tus tablas:

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.

Y la consulta, que tiene en cuenta los períodos de bloqueo totalmente superpuestos:

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.

Si su tabla black_out_dates contiene N filas, entonces hay como máximo N + 1 espacios. La consulta constituye un período de fecha de inactividad ficticia en [2011-01-03,2011-01-03] y luego utiliza la función analítica LEAD para determinar dónde comienza la siguiente fecha de exclusión. Las funciones analíticas ROW_NUMBER eliminan los períodos que se superponen por completo, ya que estropean los períodos de brecha.

EDITAR 14-3-2011

Con estas tablas:

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.

Mi consulta anterior aún no manejó correctamente los períodos superpuestos. Así que aquí hay una versión revisada, convenientemente almacenada en la vista 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.

Y los resultados de la prueba:

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 /

Saludos,
Robar.

Tengo que decir una "fecha de viaje" y fechas en blanco. Voy a dividir la fecha de viaje en pedazos de acuerdo a las fechas de black out.

Nota: La fecha de viaje puede estar entre 0 - 9999 99 99

Muestra:

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

Resultado Exceptuado:

New Travel | START DATE | END DATE X1 | 2011 02 16 | 2011 03 12 X2 | 2011 06 30 | 2011 12 11

Visualmente:

NAME : date range Travel Date : -----[--------------------------]-- A : --[------]------------------------- B : ------[---]------------------------ C : --------------[---]---------------- D : ----------------[------]-----------

Resultado:

X1 : -----------[--]-------------------- X2 : -----------------------[--------]--

Muestra 2:

Travel Date : -[--------------------------------]-- BO Date A : ----[------]------------------------- BO Date B : ----------------------[------]------- BO Date C : --------------------[---]------------ BO Date D : ------------------[------]----------- Result X1 : -[--]-------------------------------- Result X2 : -----------[------]------------------ Result X3 : -----------------------------[----]--

Muestra 3:

Travel Date : ]-----------------------------------[ BO Date A : ----[------]------------------------- BO Date B : -------------------------[---]------- BO Date C : ----------------[---]---------------- BO Date D : ------------------[------]----------- Result X1 : ---]--------------------------------- Result X2 : -----------[--]---------------------- Result X3 : -----------------------------[-------

¿Cómo puedo hacerlo usando PL SQL?

Gracias.

Aquí están las tablas y casos de prueba:

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;

Esto devuelve una lista discreta de todas las fechas que existen en el rango Fechas de viaje pero no en la lista Fechas de bloqueo, y luego las combina con Oracle - Convertir el valor de filas en rangos :

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;

Resultado:

GROUPNUM MIN(N) MAX(N) 1 16/02/2011 12/03/2011 2 30/06/2011 11/12/2011