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!
