En este ejemplo veremos como obtener el total vendido en cada día de una semana usando la construcción CTE. O sea que podremos responder a la pregunta, ¿cuánto se vendió el domingo, cuánto se vendió el lunes, cuánto se vendió el martes…., etc.?

CREATE PROCEDURE VENTAS_SEMANALES(
   tdFechax DATE)
RETURNS(
   ftnVentasDom NUMERIC(10, 2),
   ftnVentasLun NUMERIC(10, 2),
   ftnVentasMar NUMERIC(10, 2),
   ftnVentasMie NUMERIC(10, 2),
   ftnVentasJue NUMERIC(10, 2),
   ftnVentasVie NUMERIC(10, 2),
   ftnVentasSab NUMERIC(10, 2)
AS
   DECLARE VARIABLE ldFecIni DATE;
BEGIN

   ldFecIni = (SELECT :tdFechax - EXTRACT(WEEKDAY FROM :tdFechax) FROM RDB$DATABASE);

   WITH VentasDia AS (
      SELECT
         M.MVC_FECHAX,
         SUM(M.MVC_TOTALX) AS VENTAS
      FROM
         MOVIMCAB M
      WHERE
         MVC_TIPMOV = 'SVT'
      GROUP BY
         1
   )

   SELECT
      COALESCE(D0.VENTAS, 0),
      COALESCE(D1.VENTAS, 0),
      COALESCE(D2.VENTAS, 0),
      COALESCE(D3.VENTAS, 0),
      COALESCE(D4.VENTAS, 0),
      COALESCE(D5.VENTAS, 0),
      COALESCE(D6.VENTAS, 0)
   FROM
      RDB$DATABASE
   LEFT JOIN
      VENTASDIA D0
         ON D0.MVC_FECHAX = :ldFecIni + 0
   LEFT JOIN
      VENTASDIA D1
         ON D1.MVC_FECHAX = :ldFecIni + 1
   LEFT JOIN
      VENTASDIA D2
         ON D2.MVC_FECHAX = :ldFecIni + 2
   LEFT JOIN
      VENTASDIA D3
         ON D3.MVC_FECHAX = :ldFecIni + 3
   LEFT JOIN
      VENTASDIA D4
         ON D4.MVC_FECHAX = :ldFecIni + 4
   LEFT JOIN
      VENTASDIA D5
         ON D5.MVC_FECHAX = :ldFecIni + 5
   LEFT JOIN
      VENTASDIA D6
         ON D6.MVC_FECHAX = :ldFecIni + 6
   INTO
      :ftnVentasDom,
      :ftnVentasLun,
      :ftnVentasMar,
      :ftnVentasMie,
      :ftnVentasJue,
      :ftnVentasVie,
      :ftnVentasSab;

END;

Como ya sabes, una tabla CTE es una tabla virtual o sea que no existe en la realidad sino que es creada antes de usarla y es eliminada después de ser usada. Es muy útil cuando debes hacer más de un JOIN con una tabla que tiene columnas agrupadas o funciones agregadas.

¿Qué hace este stored procedure?

Primero, halla el domingo que corresponde a la semana de la fecha que recibió como parámetro de entrada. Por ejemplo, el día 18 de noviembre de 2013 es lunes, eso significa que el domingo de esa semana es el 17 de noviembre de 2013. Ese día domingo se guarda en la variable ldFecIni. ¿Por qué se hace eso? porque los días de la semana tienen número en Firebird. Domingo = 0, Lunes = 1, Martes, = 2, etc.

A continuación se crea la tabla virtual, la tabla CTE, la cual será usada más adelante en un SELECT. A esa tabla virtual (o tabla CTE) se la llamó VentasDia.

Después, se hace un SELECT a la tabla RDB$DATABASE. ¿Por qué a esa tabla? porque está garantizada de tener siempre una fila, solamente una fila, y nunca más de una fila. Aquí podrías usar cualquier otra tabla de la cual estás seguro de que siempre tendrá exactamente una fila, no es obligatorio usar RDB$DATABASE, puedes usar cualquier otra tabla que siempre tenga una fila.

Se selecciona la columna VENTAS de la tabla virtual VentasDia para cada día de la semana. ¿Cómo sabemos cuál es el día de la semana? Pues en ldFecIni tenemos el domingo. Si le sumamos 1 tendremos el lunes. Si le sumamos 2 tendremos el martes, y así sucesivamente.

Los alias a la tabla virtual los nombramos como D0, D1, D2, D3, etc. donde la letra «D» significa «día».

Finalmente, los valores de todas esas columnas los guardamos en los parámetros de salida ftnVentasDom, ftnVentasLun, etc.

Al ejecutar este stored procedure obtendremos algo como:

VENTASSEMANALES1

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

Las columnas que tienen cero implican que ese día no hubo ventas. Para ello se usó la función COALESCE() para que si el resultado de ejecutar la función SUM() era NULL lo convierta a cero.

En síntesis, este stored procedure recibe un parámetro de entrada (la fecha de un día de la semana que nos interesa) y devuelve siete parámetros de salida (el total de las ventas del domingo, el total de las ventas del lunes, el total de las ventas del martes, etc.)

Artículo relacionado:

El índice del blog Firebird21