Re: Help needed with Window function

> 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