CTEs RECURSIVOS (2)

Hace algunos meses les mostré como es posible la recursividad en SQL SERVER usando CTEs, esto fue lo que hicimos en aquella ocasión usando fechas y variables de tiempo:

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

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

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

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

Ahora les dejo otros ejemplos para calcular una secuencia de números, la serie de fibonacci y el factorial:

WITH cte AS(
 SELECT 1 AS numero
 UNION ALL
 SELECT numero+1
 FROM cte 
 WHERE numero < 1000
) SELECT * FROM cte 
OPTION ( MAXRECURSION 0 )

--fibonacci
;WITH cteFib AS(
 SELECT 1 AS numero, CAST( 0 AS BIGINT ) as fibonacci, CAST( 1 AS BIGINT ) AS numero1ant
 UNION ALL
 SELECT numero+1, fibonacci + numero1ant, fibonacci
 FROM cteFib 
 WHERE numero < 50
) 
SELECT numero, fibonacci FROM cteFib 
WHERE numero < 20
OPTION ( MAXRECURSION 0 )

--factorial
;WITH cteFac AS(
 SELECT 0 AS numero, CAST( 1 AS BIGINT ) AS factorial
 UNION ALL
 SELECT numero+1, (numero+1)*factorial
 FROM cteFac 
 WHERE numero < 20
) 
SELECT * FROM cteFac 
WHERE numero < 10
OPTION ( MAXRECURSION 0 )

Espero que les sea de ayuda.

SALUDOS!

COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE