1

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.

  • Pretty sure that's possible. Please provide proper information. Postgres version (obviously!), table definition (\d movimiento_stock in psql or CREATE TABLE script), sample data, desired result. – Erwin Brandstetter Apr 3 '15 at 17:46
  • i append the table structure, example data and expected result to my question. my postgres version is 9.4 thx – Net Wilson Apr 3 '15 at 18:26
1
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.

  • Thank you very much, the query works well. what does the WHEN method in the first part of the query? – Net Wilson Apr 4 '15 at 14:03
  • @user3712670: I added a link to the manual and a bit more explanation. CASE is a very common SQL expression – Erwin Brandstetter Apr 4 '15 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.