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
(PARTITIONBY 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 |       10ABC       | 2013-04-06 |
2013-04-06|        8ABC       | 2013-04-07 | 2013-04-07 |       10ABC       | 2013-04-08 | 2013-04-07 |        8ABC
 | 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|      10ABC       | 2013-04-06 |       8 | ABC       | 2013-04-06 |       8ABC       | 2013-04-06 |       8
|ABC       | 2013-04-05 |      10ABC       | 2013-04-07 |      10 | ABC       | 2013-04-07 |      10ABC       |
2013-04-07|      10 | ABC       | 2013-04-06 |       8ABC       | 2013-04-07 |      10 | ABC       | 2013-04-05 |
10ABC      | 2013-04-08 |      -2 | ABC       | 2013-04-08 |      -2ABC       | 2013-04-08 |      -2 | ABC       |
2013-04-07|      10ABC       | 2013-04-08 |      -2 | ABC       | 2013-04-06 |       8ABC       | 2013-04-08 |      -2
|ABC       | 2013-04-05 |      10ABC       | 2013-04-09 |      -1 | ABC       | 2013-04-09 |      -1ABC       |
2013-04-09|      -1 | ABC       | 2013-04-08 |      -2ABC       | 2013-04-09 |      -1 | ABC       | 2013-04-07 |
10ABC      | 2013-04-09 |      -1 | ABC       | 2013-04-06 |       8ABC       | 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 |
           10ABC       | 2013-04-06 | 2013-04-06    |       8 |               8ABC       | 2013-04-06 | 2013-04-05    |
    10 |              18ABC       | 2013-04-07 | 2013-04-07    |      10 |              10ABC       | 2013-04-07 |
2013-04-06   |       8 |              18ABC       | 2013-04-07 | 2013-04-05    |      10 |              28ABC       |
2013-04-08| 2013-04-08    |      -2 |              -2ABC       | 2013-04-08 | 2013-04-07    |      10 |
8ABC      | 2013-04-08 | 2013-04-06    |       8 |              16ABC       | 2013-04-08 | 2013-04-05    |      10 |
         26ABC       | 2013-04-09 | 2013-04-09    |      -1 |              -1ABC       | 2013-04-09 | 2013-04-08    |
  -2 |              -3ABC       | 2013-04-09 | 2013-04-07    |      10 |               7ABC       | 2013-04-09 |
2013-04-06   |       8 |              15ABC       | 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
qtywhich you want.  
item_code |   _date    |    max     | nett_qty
-----------+------------+------------+----------ABC       | 2013-04-05 | 2013-04-05 |       10ABC       | 2013-04-06 |
2013-04-06|        8ABC       | 2013-04-07 | 2013-04-07 |       10ABC       | 2013-04-08 | 2013-04-07 |        8ABC
 | 2013-04-09 | 2013-04-07 |        7 

Akihiro Okuno