CTEs RECURSIVOS (1)

Que tal, hoy les explicaré como generar una tabla temporal de fechas que puede serles de mucha utilidad al momento de realizar ciertas consultas; en una ocasión anterior les mostré como crear un CTE ( Common TableExpression ), en esta ocasión nos apoyaremos en este recurso para poder realizar la recursión.

Que es lo que necesitamos, un inicio, que será la base y el comienzo a partir del cual nuestro CTE se ejecutará:

WITH miCTE AS (
       SELECT CAST('20130101' AS DATE) as fecha
)
SELECT * FROM miCTE

Si ejecutamos esto obtendremos:

Lo cual no se parece en nada a lo que necesitamos, ahora bien suponiendo que queremos obtener los primeros 10 días del mes de Enero,  el final de nuestra recursión será el día 2013-01-10 además de agregar una operación que vaya incrementando nuestra, por decirlo de alguna manera, variable ( en este caso sería valor de columna ) y no olvidemos llamar a nuestro CTE que estamos creando ya que si no lo hacemos, simplemente no será RECURSION, algo como esto:

WITH miCTE AS
(
       SELECT CAST('20130101' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( d , 1 ,fecha ) as fecha
       FROM miCTE
       WHERE fecha < CAST('20130110' AS DATE)
)
SELECT * FROM miCTE

Es muy importante la instrucción UNION ALL para la ejecución de este query, también debemos recordar que es muy importante que la cantidad de columnas en ambos SELECT debe ser el mismo, así como el tipo de datos. Ahora bien, ejecutamos nuestra consulta:

Tal vez se preguntarán, porque aparece el día 10 si el operador es un  < ( menor que )  y no un <= ( menor igual que ), ésta forma de hacer la recursión es como en algunos lenguajes de programación funciona el bucle DO WHILE, primero ejecuta todo el código contenido en él y después verifica si debe continuar dentro del bucle o salirse de él, en este caso primero realiza la suma de un día, antes de validar la condición.

Otro ejemplo claro de esto, es que en ocasiones es necesario los meses de cierto periodo, pero solo los meses, por ejemplo:

WITH miCTE AS
(
       SELECT CAST('20120601' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( m , 1 ,fecha ) as fecha
       FROM miCTE WHERE fecha < CAST('20130501' AS DATE)
)
SELECT * FROM miCTE

En este caso, queremos obtener los meses solamente del período 2012-06-01 hasta 2013-05-01, por lo tanto también deberemos cambiar el parametro de DATEADD para que nos sume los meses en lugar de días y obtendremos lo siguiente:

Si deseamos obtener las horas de un día, solo hacemos unos pequeños cambios como el tipo de dato y además el valor DATEADD que debe aplicar la operación:

WITH miCTE AS
(
       SELECT CAST('00:00:00' AS TIME) as hora
       UNION ALL
       SELECT DATEADD( HH , 1 ,hora ) as hora
       FROM miCTE WHERE hora < CAST('23:00:00' AS TIME)
)
SELECT * FROM miCTE

Otro ejemplo  es como generar todas las medias horas de un día, algo así:

WITH miCTE AS
(
       SELECT CAST('00:00:00' AS TIME) as hora
       UNION ALL
       SELECT DATEADD( MINUTE , 30 ,hora ) as hora
       FROM miCTE WHERE hora < CAST('23:30:00' AS TIME)
)
SELECT * FROM miCTE

Ahora bien, todo esto funciona de manera correcta siempre y cuando el proceso no deba hacer más de 100 recursiones ya que si lo hace, SQL SERVER mandará un error como el siguiente, por ejemplo si queremos obtener los días de todo un año:

WITH miCTE AS
(
       SELECT CAST('20130101' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( d , 1 ,fecha ) as fecha
       FROM miCTE WHERE fecha < CAST('20131231' AS DATE)
)
SELECT * FROM miCTE

Esto se debe a una clausula que en caso que no la especifiquemos, el motor solo hará 100 recursiones y al realizar la 101 marcará el error. Para esto debemos especificar la clausula OPTION(MAXRECURSION valorInt ) el valor puede ser entre 1 y 32767, si se especifica 0, la recursión no tiene límite.

WITH miCTE AS
(
       SELECT CAST('20130101' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( d , 1 ,fecha ) as fecha
       FROM miCTE WHERE fecha < CAST('20131231' AS DATE)
)
SELECT * FROM miCTE
OPTION(MAXRECURSION 0)

Una clara aplicación de esto es cuando queremos conocer, por ejemplo en la base de datos AdventureWorks2012, que día hicimos compras y aquellos días que no hayamos tenido compras reemplazar con 0:

SELECT CAST( OrderDate AS DATE) as fecha, COUNT( PurchaseOrderID ) as compras
FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= '20080101' AND OrderDate < '20080121'
GROUP BY CAST( OrderDate AS DATE)
ORDER BY fecha

Como podemos observar no tenemos todos los días del período 2008-01-01 al 2008-01-20 pero queremos que aparezcan, como lo logramos? de la siguiente manera:

WITH miCTE AS
(
       SELECT CAST('20080101' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( d , 1 ,fecha ) as fecha
       FROM miCTE WHERE fecha < CAST('20080120' AS DATE)
)
SELECT *
FROM miCTE c
LEFT JOIN (
       SELECT CAST( OrderDate AS DATE) as fecha, COUNT( PurchaseOrderID ) as compras
       FROM Purchasing.PurchaseOrderHeader
       WHERE OrderDate >= '20080101' AND OrderDate < '20080121'
       GROUP BY CAST( OrderDate AS DATE)
) x
ON c.fecha = x.fecha

Y para lo cual solo bastaría hacer unos ajustes a la consulta:

WITH miCTE AS
(
       SELECT CAST('20080101' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( d , 1 ,fecha ) as fecha
       FROM miCTE WHERE fecha < CAST('20080120' AS DATE)
)
SELECT c.fecha,ISNULL( x.compras , 0 ) as compras
FROM miCTE c
LEFT JOIN (
       SELECT CAST( OrderDate AS DATE) as fecha, COUNT( PurchaseOrderID ) as compras
       FROM Purchasing.PurchaseOrderHeader
       WHERE OrderDate >= '20080101' AND OrderDate < '20080121'
       GROUP BY CAST( OrderDate AS DATE)
) x
ON c.fecha = x.fecha

Es muy útil la aplicación de CTEs recursivos, sobre todo en estos casos cuando queremos obtener cierta información que anteriormente la sacaríamos a través de establecer el valor a una celda en EXCEL y arrastrar el mouse hacia abajo. Este es uno de muchos ejemplos para los cuales se puede utilizar este tipo de recurso, más adelante les mostraré algunos otros.

SALUDOS!

COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE