Ya hemos visto varios ejemplos de tablas virtuales recursivas, pero como es un tema que le interesa a mucha gente entonces ahora veremos otro ejemplo de lo que podemos hacer.

Problema:

Queremos ver todas las fechas desde hoy hasta los siguientes 10 días.

Solución:

Listado 1.

WITH RECURSIVE FECHAS_SIGUIENTES AS (
   
   SELECT
      CURRENT_DATE AS FECHA
   FROM
      RDB$DATABASE

   UNION ALL
   
   SELECT
      DATEADD(DAY, 1, FECHA)
   FROM
      FECHAS_SIGUIENTES
   WHERE
      FECHA < DATEADD(DAY, 10, CURRENT_DATE)

)

SELECT
   *
FROM
   FECHAS_SIGUIENTES

EJEMPLO3-1

Captura 1. Si haces clic en la imagen la verás más grande

Como siempre, nuestra tabla virtual recursiva empieza con una fila no recursiva y luego se le agregan las filas recursivas. La filas recursivas siempre deben colocarse después del UNION ALL.

El algoritmo es muy sencillo. Primero, insertamos a nuestra tabla virtual la primera fila que nos interesa, luego le insertamos otra fila conteniendo la fecha del día siguiente y continuamos insertando filas mientras la fecha obtenida sea menor que la fecha actual + 10.

Desde luego que podemos usar otro número, 10 es solamente un ejemplo. Podríamos obtener 30 fechas, 60 fechas, 365 fechas, o las que necesitemos, siempre y cuando su cantidad no sea mayor a 1024 porque ese es el límite de llamadas recursivas que permite el Firebird.

¿Y para qué nos podría servir tener una tabla virtual de fechas?

Por ejemplo, para listar todas las ventas entre dos fechas, y si en una fecha no se ha realizado ventas que muestre cero. Así nos aseguraríamos que estén todas las fechas, sin que existan fechas faltantes.

RECURSION1

Captura 1. Si haces clic en la imagen la verás más grande

Como puedes ver en la Captura 1. no hubo ventas todos los días, por ejemplo no hay ventas el 1 de agosto ni el 3 de agosto. En nuestra consulta queremos que esas dos fechas también aparezcan, aunque no hayamos vendido.

Listado 2.

WITH RECURSIVE RANGO_FECHAS AS (

   SELECT
      CAST('2015/08/01' AS DATE) AS FECHA
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      DATEADD(DAY, 1, FECHA)
   FROM
      RANGO_FECHAS
   WHERE
      FECHA < CAST('2015/08/20' AS DATE)

)

SELECT
   FECHA,
   COALESCE(SUM(FAC_MONTOX), 0) AS TOTAL_VENTA
FROM
   RANGO_FECHAS
LEFT JOIN
   FACTURAS
      ON FECHA = FAC_FECVEN
GROUP BY
   FECHA

RECURSION2

Captura 2. Si haces clic en la imagen la verás más grande

Y listo. ¡¡¡Solucionado!!!

Como puedes ver en la Captura 2. se muestran todas las fechas entre el 1 de agosto y el 20 de agosto (porque esas fueron las que elegimos, podríamos haber elegido cualquier otro rango de fechas) con el total vendido cada uno de esos días. Si en una fecha no hubo ventas, entonces se muestra cero.

Conclusión:

Poder listar todo un rango de fechas es muy útil cuando queremos ver a todas esas fechas, sin importar que en la tabla relacionada haya o no haya fechas que se puedan emparejar. Tal como nos muestra la Captura 2. si en una fecha no hubo ventas, igualmente esa fecha es mostrada.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

El índice del blog Firebird21

El foro del blog Firebird21