Consulta SQL avanzada

[MarioHeredia]

Xaji,

Este es un problema comun cuando se intenta identificar cuales fueron los cambios de periodo de "algo".
Los que a nosotros nos interesa son las transiciones, los cambios de un tipo de unidad a otro tipo de unidad sin importar las fechas que tenga de mas, si? OK.

Lo primero que vamos a hacer es identificar, cuando se produce un cambio en el tipo de unidad. Y para esto usamos una funcion analitica que trae el proximo registro de la misma columna.

Código SQL:

LEAD(TIPO_UNIDAD, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMO_TIPO_UNIDAD,
 

Y con esto ya hemos avanzado mucho, porque sabemos cuando va a cambiar y cuando no. Pero aun no es suficiente.
Teniendo ahora, el proximo valor de tipo de unidad (que es lo que nos interesa agrupar por fecha) hacemos un pregunta con un decode:

"el proximo tipo de unidad es igual al actual, si? Entonces le pongamos un 1 (uno), caso contrario 0 (cero)"

Código SQL:

DECODE(TIPO_UNIDAD, NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD), 1, 0) LOGICA
 

(el NVL es simplemente para que no quede nulo y le agregue el valor del tipo de unidad actual, esto quiere decir que suponemos que el valor del tipo de unidad anterior "al primer registro" de la consulta es el mismo que el del primer registro)

Ahora, subimos un nivel de complejidad mas. Hacemos una operacion nueva usando la funcion LAG, pero esta vez con el campo LOGICA y la llamamos ANTERIOR_LOGICA. El LAG a diferencia del LEAD, no trae el proximo registro sino el anterior registro (siempre de la misma columna) generando una nueva columna.

Código SQL:

LAG(LOGICA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_LOGICA,
 

Muy bien, ahora esta en condiciones de aplicar la cuenta principal que necesitamos para agrupar por tipo de unidad, señalando las fecha mas viejas de fecha_desde y las mas nuevas de fecha_hasta.

Código SQL:

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,
 

Realizamos una cuenta, un estilo flag de control para solo mostrar lo que queremos ver (las transiciones).

Código SQL:

DECODE(MIN_FECHA_DESDE, NULL, 0, 1) CNT_FECHA_DESDE,
DECODE(MAX_FECHA_HASTA, NULL, 0, 1) CNT_FECHA_HASTA
 

Filtramos por...

Código SQL:

(CNT_FECHA_DESDE + CNT_FECHA_HASTA) >= 1
 

Y con esto terminamos la primera parte. El resultado de este select se almacena en un tabla auxiliar.

Que tengo en esa tabla? solo las transiciones, por lo tanto, solo tengo como maximo dos registros por tipo de unidad nada mas.

Y desde aqui es mucho mas facil, solo tenemos que completar algunos campos que estan nulos.
Aqui vamos a tener, los dos campos fechas (fecha_desde y fecha_hasta) y dos nuevos campos (min_fecha_desde y max_fecha_hasta)
Renombramos y completamos.

Código SQL:

DECODE(MIN_FECHA_DESDE, NULL, FECHA_DESDE, MIN_FECHA_DESDE) FECHA_DESDE,
       DECODE(MAX_FECHA_HASTA, NULL, FECHA_HASTA, MAX_FECHA_HASTA) FECHA_HASTA,
 

Aplicamos nuevamente la logica para generar LOGICA y PROXIMA_LOGICA.
Aplicamos un logica nueva para generar ANTERIOR_FECHA_DESDE y PROXIMA_FECHA_HASTA.

Código SQL:

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,
 

Ya falta poco, no desespereis !!!!!
Aplicamos la ultima logica grande, que nos dara el resultado final.

Código SQL:

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,
 

No es complicado, no es mas de lo mismo.
Quizas las condiciones cambiaron porque cambiaron las condiciones en la tabla, ya no trabajamos mas sobre CONSULTA_SQL_AVAZANDA sino sobre AUX_CONSULTA_SQL_AVAZANDA.

Un ultimo campo orden,

Código SQL:

ROW_NUMBER () OVER(PARTITION BY CODIGO, MAX_FECHA_HASTA ORDER BY MIN_FECHA_DESDE) ORDEN
 

Y filtramos por....

Código SQL:

ORDEN = 1
 

Querido Xaji, espero pueda servirte. No tengas miedo de modificar o de hacer cambios.

Cualquier nueva duda, vuelve a consultar.
Te mando un saludo grande,
Mario.