PostgreSQL Query – Sum Column Prior to Date and Show All Entries After

aggregatefunctionspostgresql

I have a database function that sum a column prior to a date and after that date show all entries one by one. that function return the data ordered by a common field and apply the sum by date every time the common field changes.

I use Postgres 9.4.

The function do the following:

-- configuramos los parametros de la funcion
CREATE OR REPLACE FUNCTION "informeMovimientoStockHacienda"("fechaDesde" timestamp without time zone, "fechaHasta" timestamp without time zone)
-- configuramos el retorno con la tabla virtual
  RETURNS SETOF informe_movimiento_stock_hacienda AS
$BODY$
-- declaro los datos de la vista y las variables
DECLARE datosInforme informe_movimiento_stock_hacienda;
DECLARE datosInformeAnterior informe_movimiento_stock_hacienda;
DECLARE stockAnterior INTEGER;
DECLARE tropaAnterior INTEGER;
BEGIN
        -- @ARTEF Informe con Saldo Anterior - carga los datos para le informe calculando el saldo anterior a una fecha

    tropaAnterior := 0;
    stockAnterior := 0;

    -- recorro y proceso los datos del informe de stock
    FOR datosInforme IN SELECT * FROM movimiento_stock WHERE fecha <= "fechaHasta" ORDER BY tropa, fecha
    LOOP
        -- chequeo si cumple la tropa
        IF tropaAnterior <> datosInforme.tropa THEN 
            IF stockAnterior <> 0 THEN
                RETURN QUERY SELECT 0 AS id, datosInformeAnterior.tropa AS tropa, "fechaDesde" AS fecha, 'Stock anterior a la fecha'::VARCHAR AS movimiento, datosInformeAnterior.usr_id AS usr_id, datosInformeAnterior.usuario AS usuario, 0 AS clasani_id, ''::VARCHAR AS clasificacion, stockAnterior AS cantidad, -1 AS tipo;

                -- vuelvo el saldo anterior a 0
                stockAnterior := 0;
            END IF;
        END IF; 

        -- chequeo si muestro los datos
        IF datosInforme.fecha < "fechaDesde"  THEN
            stockAnterior := stockAnterior + datosInforme.cantidad;
        ELSE

            -- devuelvo la fila con el saldo anterior
            IF stockAnterior <> 0 THEN
                RETURN QUERY SELECT 0 AS id, datosInforme.tropa AS tropa, "fechaDesde" AS fecha, 'Stock anterior a la fecha'::VARCHAR AS movimiento, datosInforme.usr_id AS usr_id, datosInforme.usuario AS usuario, 0 AS clasani_id, ''::VARCHAR AS clasificacion, stockAnterior AS cantidad, -1 AS tipo;
            END IF;

            -- vuelvo el saldo anterior a 0
            stockAnterior := 0;

            -- traigo los conceptos de las filas
            RETURN NEXT datosInforme;               
        END IF;

        datosInformeAnterior := datosInforme;
        tropaAnterior := datosInforme.tropa;

    END LOOP;

    -- si sali del bucle y me falta mostrar un dato lo muestro
    IF stockAnterior <> 0 THEN
        RETURN QUERY SELECT 0 AS id, datosInformeAnterior.tropa AS tropa, "fechaDesde" AS fecha, 'Stock anterior a la fecha'::VARCHAR AS movimiento, datosInformeAnterior.usr_id AS usr_id, datosInformeAnterior.usuario AS usuario, 0 AS clasani_id, ''::VARCHAR AS clasificacion, stockAnterior AS cantidad, -1 AS tipo;

        -- vuelvo el saldo anterior a 0
        stockAnterior := 0;
    END IF;

END;
$BODY$ LANGUAGE plpgsql;

Table structure:

CREATE TABLE movimiento_stock (
  id serial NOT NULL,
  tropa integer,
  fecha timestamp without time zone,
  detalle text,
  usr_id integer,
  usuario text,
  clasani_id integer,
  clasificacion text,
  cantidad numeric(19,3),
  tipo integer,
  CONSTRAINT pk_movimiento_stock PRIMARY KEY (id)
);

Example data:

id  |tropa  |fecha      |detalle    |usr_id |usuario    |clasani_id |clasificacion  |cantidad   |tipo
1   |1000   |2015-03-01 |S Inicial  |1      |USR1       |1          |VA             |30         |1
2   |1000   |2015-03-05 |Entrada    |1      |USR1       |1          |VA             |30         |1
3   |2000   |2015-03-05 |S Inicial  |2      |USR2       |1          |VA             |50         |1
4   |1000   |2015-03-06 |Entrada    |1      |USR1       |1          |VA             |10         |1
5   |2000   |2015-03-07 |Entrada    |2      |USR2       |1          |VA             |20         |1
6   |1000   |2015-03-10 |Salida     |1      |USR1       |1          |VA             |-10        |1

Desired result:

tropa   |fecha      |detalle    |usr_id |usuario    |clasani_id |clasificacion  |cantidad   |saldo - SUM(cantidad)
1000    |2015-03-05 |S. Ant     |1      |USR1       |1          |VA             |60         |60 (SUM BEFORE 2015-03-06 FOR tropa 1000)
1000    |2015-03-06 |Entrada    |1      |USR1       |1          |VA             |10         |70
1000    |2015-03-10 |Salida     |1      |USR1       |1          |VA             |-10        |60
2000    |2015-03-05 |S. Ant     |2      |USR2       |1          |VA             |50         |50 (SUM BEFORE 2015-03-06 FOR tropa 2000)
2000    |2015-03-07 |Entrada    |2      |USR2       |1          |VA             |20         |70

This function works but is difficult to maintain and make changes and we want to know if is possible to do the same thing using a query.

Best Answer

WITH parametros AS (
   SELECT '2015-03-06'::timestamp AS fecha_desde  -- provide parameters here
        , '2015-03-12'::timestamp AS fecha_hasta
   )
SELECT tropa, max(fecha) AS fecha, sum(cantidad) AS sum_cantidad
     , sum(sum(cantidad)) OVER (PARTITION BY tropa
                                ORDER BY max(fecha)) AS saldo
FROM   movimiento_stock, parametros p
WHERE  fecha <= p.fecha_hasta
GROUP  BY tropa, CASE WHEN fecha >= p.fecha_desde THEN id END
ORDER  BY 1, 2;

The core feature is bold. Read the manual about CASE.
Older rows get NULL for id (the default in a CASE expression); id is NOT NULL in the underlying table, so collisions are not possible.

This groups all rows older than fecha_desde into one group per tropa and leaves newer rows ungrouped (form individual "groups"). It's unclear how to aggregate other columns, so I only included fecha.

The same, wrapped into an SQL function:

CREATE OR REPLACE FUNCTION informe_tldr(fecha_desde timestamp
                                      , fecha_hasta timestamp)
  RETURNS TABLE(tropa int, fecha timestamp, sum_cantidad int, saldo int) AS
$func$
SELECT m.tropa, max(m.fecha), sum(m.cantidad)::int
     , sum(sum(m.cantidad)) OVER (PARTITION BY m.tropa
                                  ORDER BY max(m.fecha))::int AS saldo
FROM   movimiento_stock m
WHERE  m.fecha <= fecha_hasta
GROUP  BY m.tropa, CASE WHEN fecha >= fecha_desde THEN m.id END
ORDER  BY 1, 2;
$func$ LANGUAGE sql;

SQL Fiddle.

Related Question