> This is an approach I also considered, but hoped for a solution without the > expense (albeit small) of having to create a function.
How about this query? ---- CREATE TABLE transactions ( item_code text, _date date, qty double precision ) ; INSERT INTO transactions VALUES ('ABC','2013-04-05',10.00), ('ABC','2013-04-06',10.00), ('ABC','2013-04-06',-2.00), ('ABC','2013-04-07',10.00), ('ABC','2013-04-08',-2.00), ('ABC','2013-04-09',-1.00) ; WITH aggregated_transactions AS ( SELECT item_code, _date, sum(qty) AS sum_qty FROM transactions GROUP BY item_code, _date ) SELECT item_code, _date, max(nett_qty_date), (array_agg(accumulated_qty ORDER BY _date DESC))[1] AS nett_qty FROM ( SELECT t1.item_code, t1._date, t2._date AS nett_qty_date, sum(t2.sum_qty) OVER (PARTITION BY t1.item_code, t1._date ORDER BY t2._date DESC) AS accumulated_qty FROM aggregated_transactions t1 INNER JOIN aggregated_transactions t2 ON t1.item_code = t2.item_code AND t1._date >= t2._date ) t WHERE accumulated_qty >= 0 GROUP BY item_code, _date ; item_code | _date | max | nett_qty -----------+------------+------------+---------- ABC | 2013-04-05 | 2013-04-05 | 10 ABC | 2013-04-06 | 2013-04-06 | 8 ABC | 2013-04-07 | 2013-04-07 | 10 ABC | 2013-04-08 | 2013-04-07 | 8 ABC | 2013-04-09 | 2013-04-07 | 7 ---- Rough explanation: 1. List the past date for each date using self join. item_code | _date | sum_qty | item_code | _date | sum_qty -----------+------------+---------+-----------+------------+--------- ABC | 2013-04-05 | 10 | ABC | 2013-04-05 | 10 ABC | 2013-04-06 | 8 | ABC | 2013-04-06 | 8 ABC | 2013-04-06 | 8 | ABC | 2013-04-05 | 10 ABC | 2013-04-07 | 10 | ABC | 2013-04-07 | 10 ABC | 2013-04-07 | 10 | ABC | 2013-04-06 | 8 ABC | 2013-04-07 | 10 | ABC | 2013-04-05 | 10 ABC | 2013-04-08 | -2 | ABC | 2013-04-08 | -2 ABC | 2013-04-08 | -2 | ABC | 2013-04-07 | 10 ABC | 2013-04-08 | -2 | ABC | 2013-04-06 | 8 ABC | 2013-04-08 | -2 | ABC | 2013-04-05 | 10 ABC | 2013-04-09 | -1 | ABC | 2013-04-09 | -1 ABC | 2013-04-09 | -1 | ABC | 2013-04-08 | -2 ABC | 2013-04-09 | -1 | ABC | 2013-04-07 | 10 ABC | 2013-04-09 | -1 | ABC | 2013-04-06 | 8 ABC | 2013-04-09 | -1 | ABC | 2013-04-05 | 10 2. Calculate an accumulated qty value using window function sorted by date in descending order. item_code | _date | nett_qty_date | sum_qty | accumulated_qty -----------+------------+---------------+---------+----------------- ABC | 2013-04-05 | 2013-04-05 | 10 | 10 ABC | 2013-04-06 | 2013-04-06 | 8 | 8 ABC | 2013-04-06 | 2013-04-05 | 10 | 18 ABC | 2013-04-07 | 2013-04-07 | 10 | 10 ABC | 2013-04-07 | 2013-04-06 | 8 | 18 ABC | 2013-04-07 | 2013-04-05 | 10 | 28 ABC | 2013-04-08 | 2013-04-08 | -2 | -2 ABC | 2013-04-08 | 2013-04-07 | 10 | 8 ABC | 2013-04-08 | 2013-04-06 | 8 | 16 ABC | 2013-04-08 | 2013-04-05 | 10 | 26 ABC | 2013-04-09 | 2013-04-09 | -1 | -1 ABC | 2013-04-09 | 2013-04-08 | -2 | -3 ABC | 2013-04-09 | 2013-04-07 | 10 | 7 ABC | 2013-04-09 | 2013-04-06 | 8 | 15 ABC | 2013-04-09 | 2013-04-05 | 10 | 25 3. Select the max date which have a positive accumulated qty value. The accumulated qty value for that date is a nett qty which you want. item_code | _date | max | nett_qty -----------+------------+------------+---------- ABC | 2013-04-05 | 2013-04-05 | 10 ABC | 2013-04-06 | 2013-04-06 | 8 ABC | 2013-04-07 | 2013-04-07 | 10 ABC | 2013-04-08 | 2013-04-07 | 8 ABC | 2013-04-09 | 2013-04-07 | 7 Akihiro Okuno -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql