> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
