Consulta SQL avanzada

[MarioHeredia]

Hola Xaji,

Voy a copiar aqui, las dos consultas (primera parte y segunda parte) luego las aplicamos paso por paso al final, te parece?

PARTE 1 (Creacion de tabla, insercion de datos y creacion de tabla aux)

Código SQL:

DROP TABLE CONSULTA_SQL_AVANZADA PURGE;
 
CREATE TABLE CONSULTA_SQL_AVANZADA
(
CODIGO VARCHAR2(10),
FECHA_DESDE DATE,
FECHA_HASTA DATE,
TIPO_UNIDAD NUMBER
) TABLESPACE AUXILIAR PCTFREE 5 PCTUSED 95;
 
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('10.03.2002', 'DD.MM.YYYY'), TO_DATE('10.03.2002', 'DD.MM.YYYY'), 1);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('11.03.2002', 'DD.MM.YYYY'), TO_DATE('11.03.2002', 'DD.MM.YYYY'), 9);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('12.03.2002', 'DD.MM.YYYY'), TO_DATE('15.03.2002', 'DD.MM.YYYY'), 9);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('16.03.2002', 'DD.MM.YYYY'), TO_DATE('23.04.2002', 'DD.MM.YYYY'), 1);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('24.04.2002', 'DD.MM.YYYY'), TO_DATE('25.04.2002', 'DD.MM.YYYY'), 1);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('26.04.2002', 'DD.MM.YYYY'), TO_DATE('23.06.2002', 'DD.MM.YYYY'), 1);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('24.06.2002', 'DD.MM.YYYY'), TO_DATE('06.08.2002', 'DD.MM.YYYY'), 9);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('07.08.2002', 'DD.MM.YYYY'), TO_DATE('09.09.2002', 'DD.MM.YYYY'), 9);
INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('10.09.2002', 'DD.MM.YYYY'), TO_DATE('18.09.2002', 'DD.MM.YYYY'), 9);
COMMIT;
 
 
DROP TABLE AUX_CONSULTA_SQL_AVANZADA PURGE;
 
CREATE TABLE AUX_CONSULTA_SQL_AVANZADA TABLESPACE AUXILIAR PCTFREE 5 PCTUSED 95 AS
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD,
       MIN_FECHA_DESDE,
       MAX_FECHA_HASTA
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD,
       PROXIMO_TIPO_UNIDAD,
       LOGICA,
       ANTERIOR_LOGICA,
       MIN_FECHA_DESDE,
       MAX_FECHA_HASTA,
       ORDEN,
       MIN_ORDEN,
       MAX_ORDEN,
       DECODE(MIN_FECHA_DESDE, NULL, 0, 1) CNT_FECHA_DESDE,
       DECODE(MAX_FECHA_HASTA, NULL, 0, 1) CNT_FECHA_HASTA
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD,
       PROXIMO_TIPO_UNIDAD,
       LOGICA,
       ANTERIOR_LOGICA,
       DECODE(ORDEN, MIN_ORDEN, FECHA_DESDE, 
       CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN NULL
            WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN FECHA_DESDE
            ELSE NULL END) MIN_FECHA_DESDE,
       DECODE(ORDEN, MAX_ORDEN, FECHA_HASTA, 
       CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN FECHA_HASTA
            WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN NULL
            ELSE NULL END) MAX_FECHA_HASTA,
       ORDEN,
       MIN_ORDEN,
       MAX_ORDEN
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD,
       PROXIMO_TIPO_UNIDAD,
       LOGICA,
       LAG(LOGICA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_LOGICA,
       ORDEN,
       MIN(ORDEN) OVER() MIN_ORDEN,
       MAX(ORDEN) OVER() MAX_ORDEN
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD,
       NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD) PROXIMO_TIPO_UNIDAD,
       ORDEN,
       DECODE(TIPO_UNIDAD, NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD), 1, 0) LOGICA
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD,
       LEAD(TIPO_UNIDAD, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMO_TIPO_UNIDAD,
       ROW_NUMBER() OVER(ORDER BY FECHA_DESDE) ORDEN
  FROM CONSULTA_SQL_AVANZADA
       )
       )
       )
       )
       )
WHERE (CNT_FECHA_DESDE + CNT_FECHA_HASTA) >= 1;
 

PARTE 2 (Select final)

Código SQL:

SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD
  FROM (
SELECT CODIGO,
       MIN_FECHA_DESDE FECHA_DESDE,
       MAX_FECHA_HASTA FECHA_HASTA,
       TIPO_UNIDAD,
       ROW_NUMBER () OVER(PARTITION BY CODIGO, MAX_FECHA_HASTA ORDER BY MIN_FECHA_DESDE) ORDEN
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       TIPO_UNIDAD,
       PROXIMO_TIPO_UNIDAD,
       LOGICA,
       ANTERIOR_LOGICA,
       DECODE(ORDEN, MIN_ORDEN, FECHA_DESDE, 
       CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN ANTERIOR_FECHA_DESDE
            WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN FECHA_DESDE
            ELSE FECHA_DESDE END) MIN_FECHA_DESDE,
       DECODE(ORDEN, MAX_ORDEN, FECHA_HASTA, 
       CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN FECHA_HASTA
            WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN PROXIMA_FECHA_HASTA
            ELSE FECHA_HASTA END) MAX_FECHA_HASTA,
       ORDEN,
       MIN_ORDEN,
       MAX_ORDEN
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       ANTERIOR_FECHA_DESDE,
       PROXIMA_FECHA_HASTA,
       TIPO_UNIDAD,
       PROXIMO_TIPO_UNIDAD,
       LOGICA,
       LAG(LOGICA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_LOGICA,
       ORDEN,
       MIN(ORDEN) OVER() MIN_ORDEN,
       MAX(ORDEN) OVER() MAX_ORDEN
  FROM (
SELECT CODIGO,
       FECHA_DESDE,
       FECHA_HASTA,
       LAG(FECHA_DESDE, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_FECHA_DESDE,
       LEAD(FECHA_HASTA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMA_FECHA_HASTA,
       TIPO_UNIDAD,
       NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD) PROXIMO_TIPO_UNIDAD,
       ORDEN,
       DECODE(TIPO_UNIDAD, NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD), 1, 0) LOGICA
  FROM (
SELECT CODIGO,
       DECODE(MIN_FECHA_DESDE, NULL, FECHA_DESDE, MIN_FECHA_DESDE) FECHA_DESDE,
       DECODE(MAX_FECHA_HASTA, NULL, FECHA_HASTA, MAX_FECHA_HASTA) FECHA_HASTA,
       TIPO_UNIDAD,
       LEAD(TIPO_UNIDAD, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMO_TIPO_UNIDAD,
       ROW_NUMBER() OVER(ORDER BY FECHA_DESDE) ORDEN
  FROM AUX_CONSULTA_SQL_AVANZADA
       )
       )
       )
       )
       )
 WHERE ORDEN = 1;