Restando fechas que se encuentran en distintas filas

Deja un comentario

Un lector del foro de este blog, preguntó si era posible restar fechas que se encuentran en distintas filas.

Restar dos fechas de distintos registros

FECHAS01

Captura 1.

La respuesta es afirmativa, pero necesitamos conocer sin equivocación posible a la fila donde se encuentra la segunda fecha.

Por eso, en este caso supondremos que se encuentra en la siguiente fila. O sea, la Fecha Inicial estará en una fila y la Fecha Final siempre estará en la siguiente fila.

¿Y cómo podemos conocer cual es la siguiente fila?

Pues numerando a las filas, de tal manera que la primera tenga el número 1, la segunda tenga el número 2, la tercera tenga el número 3, etc.

Este artículo nos muestra algunas técnicas para conseguir lo que deseamos:

Numerando las filas de un SELECT

Lo que aquí haremos (no es el único método, pero es un método que se entiende bien) es agregarle una columna a nuestra tabla. Nos quedará así:

FECHAS02

Captura 2.

PRU_NUMREG es el Número de Registro

PRU_FECINI es la Fecha Inicial

PRU_FECFIN es la Fecha Final

Luego creamos un generador (también llamado secuencia) y con un comando UPDATE numeraremos a esas filas:

Listado 1.

CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

Como el generador ya no nos sirve, lo eliminamos con el comando DROP.

El contenido de nuestra tabla ahora quedará así:

FECHAS03

Captura 3.

Como tenemos a todas las filas numeradas el siguiente paso ya es muy fácil:

Listado 2:

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1),
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1) - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1

Podemos tener a todo junto:

Listado 3.

CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1),
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1) - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1;

Y este será el resultado que obtendremos:

FECHAS04

Captura 4.

Que es justamente lo que queríamos conseguir: restarle a la Fecha Inicial que se encuentra en una fila la Fecha Final que se encuentra en la siguiente fila.

Desde luego que este no es el único método posible para obtener este resultado, pero es un método fácil y sencillo de entender.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Ejemplo de recursión (3). Fechas consecutivas

Deja un comentario

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

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

 

Facturas que vencen hoy, en 7 días, en 15 días y en 30 días

Deja un comentario

Si necesitamos consultar cuales son las Facturas que debemos cobrar hoy, que debemos cobrar en 7 días, en 15 días, en 30 días (y por supuesto, en cualquier otra fecha) podemos hacerlo de varias maneras, aquí hay cuatro de ellas:

Ejemplo:

Tenemos una tabla llamada FACTURAS donde se encuentran las fechas de vencimiento de las Facturas de venta, la tabla tiene esta estructura:

FACTURAS1

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

y contiene estas filas:

FACTURAS2

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

Método 1. Usando UNION

Podemos usar el comando UNION para unir el resultado de una consulta con el resultado de otra consulta. En este caso, para diferenciar los resultados y saber a cual consulta pertenecen los resultados debemos agregarle una columna a cada consulta. En este ejemplo esa columna agregada se llama FECHA.

CREATE VIEW V_VENCIMIENTOS(
   FECHA,
   FAC_FECVEN,
   FAC_NUMERO,
   FAC_MONTOX)
AS

   SELECT
      'HOY' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN = CURRENT_DATE

   UNION

   SELECT
      'SEMANA' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
     FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6

   UNION

   SELECT
      'QUINCENA' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14

   UNION

   SELECT
      'MES' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30;

Aquí, a las Facturas que vencen hoy les unimos las que vencen en la semana, les unimos las que vencen en la quincena y les unimos las que vencen en el mes. Se usa la cláusula BETWEEN para delimitar las fechas de las Facturas que serán mostradas y evitar así que una Factura se muestre más de una vez.

Escribimos esta consulta:

SELECT * FROM V_VENCIMIENTOS ORDER BY FAC_FECVEN

Y este es el resultado que obtenemos:

FACTURAS3

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

Donde cada Factura aparece en una fila y en la primera columna podemos ver si su vencimiento corresponde al día de hoy, a la semana, a la quincena o al mes.

Método 2. Usando un stored procedure

Aquí, en lugar de mostrar los vencimientos en filas los mostramos en columnas porque para algunas personas puede ser más descriptivo de esta manera:

CREATE PROCEDURE VENCIMIENTOS
   RETURNS(
      ftcNumero1 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto1  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero2 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto2  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero3 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto3  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero4 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto4  TYPE OF COLUMN FACTURAS.FAC_MONTOX)
AS
   DECLARE VARIABLE lcNumero TYPE OF COLUMN FACTURAS.FAC_NUMERO;
   DECLARE VARIABLE lnMontox TYPE OF COLUMN FACTURAS.FAC_MONTOX;
   DECLARE VARIABLE ldFecVen TYPE OF COLUMN FACTURAS.FAC_FECVEN;
BEGIN

   FOR SELECT
      FAC_NUMERO,
      FAC_MONTOX,
      FAC_FECVEN
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
   ORDER BY
      FAC_FECVEN
   INTO
      :lcNumero,
      :lnMontox,
      :ldFecVen
   DO BEGIN
      ftcNumero1 = NULL;
      ftnMonto1  = NULL;
      ftcNumero2 = NULL;
      ftnMonto2  = NULL;
      ftcNumero3 = NULL;
      ftnMonto3  = NULL;
      ftcNumero4 = NULL;
      ftnMonto4  = NULL;
      IF (ldFecVen = CURRENT_DATE) THEN BEGIN
         ftcNumero1 = lcNumero;
         ftnMonto1  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6) THEN BEGIN
         ftcNumero2 = lcNumero;
         ftnMonto2  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14) THEN BEGIN
         ftcNumero3 = lcNumero;
         ftnMonto3  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30) THEN BEGIN
         ftcNumero4 = lcNumero;
         ftnMonto4  = lnMontox;
      END
      SUSPEND;
   END

END;

Como se trata de un stored procedure seleccionable lo ejecutamos así:

SELECT * FROM VENCIMIENTOS

Y este será el resultado que obtendremos:

FACTURAS3

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

Si no quieres ver los Null puedes usar la función COALESCE() para que desaparezcan.

Método 3. Usando una vista con una tabla CTE

Ya hemos visto en artículos anteriores lo útiles que pueden ser las tablas CTE, aquí también pueden ser usadas:

CREATE VIEW V_VENCIMIENTOS(
   FAC_FECVEN,
   HOY,
   FAC_MONTOX,
   SEMANA,
   FAC_MONTOX1,
   QUINCENA,
   FAC_MONTOX2,
   MES,
   FAC_MONTOX3)
AS

   WITH FacturasVencen AS (
      SELECT
         FAC_NUMERO,
         FAC_MONTOX,
         FAC_FECVEN
      FROM
         FACTURAS
   )

   SELECT
      F.FAC_FECVEN,
      F1.FAC_NUMERO AS "HOY",
      F1.FAC_MONTOX,
      F2.FAC_NUMERO AS "SEMANA",
      F2.FAC_MONTOX,
      F3.FAC_NUMERO AS "QUINCENA",
      F3.FAC_MONTOX,
      F4.FAC_NUMERO AS "MES",
      F4.FAC_MONTOX
   FROM
      FACTURAS F
   LEFT JOIN
      FacturasVencen F1
         ON F.FAC_NUMERO = F1.FAC_NUMERO AND
            F1.FAC_FECVEN = CURRENT_DATE
   LEFT JOIN
      FacturasVencen F2
         ON F.FAC_NUMERO = F2.FAC_NUMERO AND
            F2.FAC_FECVEN BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6
   LEFT JOIN
      FacturasVencen F3
         ON F.FAC_NUMERO = F3.FAC_NUMERO AND
            F3.FAC_FECVEN BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14
   LEFT JOIN
      FacturasVencen F4
         ON F.FAC_NUMERO = F4.FAC_NUMERO AND
            F4.FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30
   WHERE
      F1.FAC_FECVEN IS NOT NULL OR
      F2.FAC_FECVEN IS NOT NULL OR
      F3.FAC_FECVEN IS NOT NULL OR
      F4.FAC_FECVEN IS NOT NULL;

Y al ejecutar a esa vista con este comando:

SELECT * FROM V_VENCIMIENTOS ORDER BY FAC_FECVEN

Este será el resultado que obtendremos:

FACTURAS4

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

Si no quieres ver los Null puedes usar la función COALESCE() para que desaparezcan.

Método 4. Usando una tabla en el FROM

También puedes ver las Facturas en filas con esta consulta:

SELECT
   *
FROM (
   SELECT
      CASE WHEN FAC_FECVEN = CURRENT_DATE THEN 'HOY'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE +  1 AND CURRENT_DATE +  6 THEN 'SEMANA'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE +  7 AND CURRENT_DATE + 14 THEN 'QUINCENA'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30 THEN 'MES'
      END AS FECHA_VENCIMIENTO,
      FAC_NUMERO,
      FAC_FECVEN,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
 ) AS MiTabla
ORDER BY
   FAC_FECVEN

Y este será el resultado obtenido:

FACTURAS5

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

Conclusión:

Con todos los métodos obtenemos las Facturas que debemos cobrar pero la forma de salida de los resultados es diferente, a veces una de las salidas es la preferible. También en algunos métodos se escribe mucho más que en otros.

Artículo relacionado:

El índice del blog Firebird21