Ejemplo de recursión (7). Números faltantes en una serie

1 comentario

Si en una tabla o en una vista o en un procedimiento almacenado seleccionable, tenemos una columna numérica y queremos saber si están todos los números o si falta alguno, podemos usar la técnica mostrada en este artículo para averiguarlo.

Para ello, mediante recursión crearemos una tabla virtual, que en nuestro ejemplo llamaremos RANGO_NUMEROS. Esa tabla virtual contendrá todos los números que nos interesan, de forma consecutiva. Es decir: 1, 2, 3, 4, 5, 6, 7, 8, 9, …

Desde luego que podemos empezar con cualquier número, no es obligatorio que empecemos con el número 1.

La tabla es virtual porque solamente existe en la memoria de la computadora y mientras dure la ejecución del SELECT principal, no existe dentro de la Base de Datos ni tampoco se guarda en el disco duro, solamente existe hasta que el SELECT principal finaliza, luego … desaparece totalmente.

Listado 1. Un SELECT para averiguar si hay números consecutivos faltantes

WITH RECURSIVE RANGO_NUMEROS AS (

   SELECT
      1 AS NUMERO
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      NUMERO + 1 AS NUMERO
   FROM
      RANGO_NUMEROS
   WHERE
      NUMERO <= 36
)

SELECT
   NUMERO
FROM
   RANGO_NUMEROS
LEFT JOIN
   REVALUOSCAB
      ON NUMERO = RVC_IDENTI
WHERE
   RVC_IDENTI IS NULL

Como siempre que usamos recursión, debemos asignar el valor inicial (en nuestro ejemplo, es el número 1, pero puedes elegir otro número si quieres) y un valor final (en nuestro ejemplo, es 36).

El valor final es absolutamente necesario establecerlo porque de lo contrario la recursión continuaría indefinidamente. Bueno, en realidad, hasta que llegues al límite de recursiones permitidas o hasta que la computadora se quede sin memoria RAM.

Siempre que uses recursión debes establecer una condición de salida, es decir, una condición para que la recursión finalice. No tendría sentido de otro modo.

¿Que hicimos en el Listado 1.?

Primero, hemos creado una tabla virtual llamada RANGO_NUMEROS, cuyo contenido es una sola columna, llamada NUMERO, y cuyos valores van desde el 1 hasta el 37 de forma consecutiva, es decir sin que falte algún número. Están todos. Va hasta el 37 porque en el SELECT pusimos NUMERO + 1. Y como en el WHERE pusimos 36, entonces obtendremos un número más, en este caso 37.

Segundo, hemos hecho un LEFT JOIN de nuestra tabla virtual llamada RANGO_NUMEROS con la tabla REVALUOSCAB, la cual tiene los números que queremos verificar.

Tercero, pusimos la condición RVC_IDENTI IS NULL para que solamente nos muestre los números que están en la tabla virtual RANGO_NUMEROS y que no están en la tabla REVALUOSCAB. De esta manera, solamente los números que se encuentren en la tabla virtual RANGO_NUMEROS y que no se encuentren en la tabla REVALUOSCAB obtendremos en el resultado.

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

En la Captura 1. vemos el contenido de la columna RVC_IDENTI de la tabla REVALUOSCAB. Como puedes notar, faltan los números que van del 26 al 36.

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

Después de ejecutar el Listado 1. obtenemos como resultado lo que vemos en la Captura 2., es decir, todos los números faltantes.

Conclusión:

Hay varias técnicas para mostrar los números que faltan en una serie, en este artículo hemos visto una de esas técnicas, la cual emplea recursión. Saber usar recursión puede ayudarte en muchos casos, por lo tanto es muy bueno conocer como usarla.

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

Ejemplo de recursión (3). Fechas consecutivas

Ejemplo de recursión (4). Actualizando filas recursivamente

Ejemplo de recursión (5). Saldos acumulados

Ejemplo de recursión (6). Repitiendo las filas

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Hallar los movimientos ocurridos entre dos fechas (otro método)

5 comentarios

En este artículo ya habíamos visto un método para hallar todos los movimientos (compras, ventas, cobranzas, pagos, etc.) que ocurrieron entre dos fechas dadas, y si en una fecha no hubo movimientos entonces mostrarla igual pero con un total de cero.

https://firebird21.wordpress.com/2014/04/29/hallando-todas-las-ventas-entre-dos-fechas-dadas/

Ahora, veremos otro método con el cual podremos obtener los mismos resultados, gracias a la colaboración de Claudio Martín.

Aquí, lo que haremos será crear un stored procedure seleccionable que nos devolverá todas las fechas de un rango dado, y luego cuando las necesitemos mediante un LEFT JOIN o un RIGHT JOIN las obtendremos.

Este método tiene la ventaja de que el stored procedure seleccionable que escribimos es uno solo y lo podemos utilizar en multitud de ocasiones diferentes, todas las veces que necesitemos un rango de fechas.


CREATE PROCEDURE RANGO_FECHAS(
      tdFecIni DATE,
      tdFecFin DATE)
   RETURNS(
      ftdFecha DATE)
AS
   DECLARE VARIABLE ldFecha DATE;
BEGIN

   ldFecha = tdFecIni;

   WHILE (ldFecha <= tdFecFin) DO BEGIN
      ftdFecha = ldFecha;
      SUSPEND;
      ldFecha = ldFecha + 1;
   END

END;

Entonces, usando la misma tabla que en el artículo anterior, escribiríamos:

SELECT
   R.ftdFecha,
   SUM(COALESCE(M.MVC_TOTALX, 0)) AS TOTAL_VENTAS_DIA
FROM
   RANGO_FECHAS('01/01/2014', '01/07/2014') R
LEFT JOIN
   MOVIMCAB M
      ON R.ftdFecha = M.MVC_FECHAX
GROUP BY
   R.ftdFecha

Y obtendríamos este resultado:

VENTAS1

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

El cual, como puedes verificar, es exactamente igual al obtenido con el otro método. Pero la ventaja de este nuevo método es que el stored procedure seleccionable podemos usarlo siempre que necesitemos obtener todas las fechas de un rango dado.

 Conclusión:

Una de las muchas cosas buenas que tiene el Firebird es que nos permite obtener los mismos resultados usando métodos diferentes, queda a nuestro criterio elegir el que nos parece más conveniente para cada situación particular.

En este caso, podemos tener un stored procedure que cuando las necesitemos nos devolverá todas las fechas de un rango, para ello simplemente lo juntamos mediante LEFT JOIN o RIGHT JOIN a la otra tabla y listo, ya está.

Artículos relacionados:

Hallando todas las ventas entre dos fechas dadas

Entendiendo a los Stored Procedures

El índice del blog Firebird21

 

Hallando todas las ventas entre dos fechas dadas

6 comentarios

En ocasiones podríamos necesitar ver todos los movimientos (compras, ventas, cobranzas, pagos, etc.) que ocurrieron entre dos fechas dadas, pero queremos que si en una fecha no hubo movimientos nos muestre cero.

Eso no podemos resolverlo con un SELECT porque el SELECT solamente nos mostrará los movimientos ocurridos, y si una fecha no tuvo movimientos entonces no será mostrada.

La solución es escribir un stored procedure seleccionable, el cual nos dará la información que necesitamos.

Ejemplo. Ver todas las ventas realizadas entre los días 01/ENE/2014 y 07/ENE/2014

Nuestra tabla MOVIMCAB (donde registramos la cabecera de los movimientos) tiene estos datos (y varios más que ahora no nos interesan):

VENTAS1

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

Escribimos este stored procedure:

CREATE PROCEDURE VENTAS_DIARIAS(
      tdFecIni DATE,
      tdFecFin DATE)
   RETURNS(
      ftdFechax DATE,
      ftnTotalx INTEGER)
AS
   DECLARE VARIABLE ldFecha DATE;
BEGIN

   ldFecha = tdFecIni;

   WHILE (ldFecha <= tdFecFin) DO BEGIN
      ftdFechax = ldFecha;
      ftnTotalx = (SELECT SUM(MVC_TOTALX) FROM MOVIMCAB WHERE MVC_FECHAX = :ldFecha);
      ftnTotalx = COALESCE(ftnTotalx, 0);
      SUSPEND;
      ldFecha = ldFecha + 1;
   END

END;

Y como es un stored procedure seleccionable (sabemos eso porque tiene el comando SUSPEND dentro suyo) lo ejecutamos así:

SELECT
   *
FROM
   VENTAS_DIARIAS('01/01/2014', '01/07/2014')

Para que nos muestre todas las ventas ocurridas entre los días 1 de enero de 2014 y 7 de enero de 2014, agrupadas por fecha. Y este es el resultado que obtenemos:

VENTAS2

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

Donde, como puedes ver, se muestran todas las fechas del rango elegido, si en una fecha hubo ventas el total de las ventas de esa fecha y si no hubo ventas, entonces cero.

Artículos relacionados:

Entendiendo a los Stored Procedures

El índice del blog Firebird21