fifo stock calculation with window function

From: Dmitry Teslenko <dteslenko(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: fifo stock calculation with window function
Date: 2015-01-16 10:07:22
Message-ID: CAPBNEqO0z2NptKQ8LtjGjgYu=LvgSFN=5gkT6Ca9O3HzPLAahA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Inspired by https://callmeranjeet.wordpress.com/2014/09/01/calculating-stock-with-fifo-method-in-sql/

Here's table which stores stock incomes and sales

CREATE TABLE operations (
id integer,
db_product_id integer,
kr_product_id integer,
qty numeric(20,3),
db_account character varying(255),
kr_account character varying(255),
mark integer
);

INSERT INTO operations VALUES (62310025, 96028, 0, 5.000, '001', '002', NULL);
INSERT INTO operations VALUES (62371515, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62385513, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62757794, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62757795, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62757796, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62910647, 96028, 0, 5.000, '001', '002', NULL);
INSERT INTO operations VALUES (62416041, 0, 96028, 1.000, '003', '001', 1);

and this sql queries incomes and shows what qty were before income
operation, what qty were added to stock, how many items from that
income where sold later, and what qty left:

select
(s.bal - s.qty)::float as before
,s.qty::float as in_
,((s.bal - s.qty) + s.qty - greatest(0, s.sum_))::float as out_
,greatest(0, s.sum_)::float as after
from
(
select
id
,coalesce(db_product_id, kr_product_id) as prd
,case when db_account = '001' then 1 else 0 end as dir
,case when db_account = '001' then qty else 0 - qty end as qty
,sum(case when db_account = '001' then qty else 0 - qty end)
over (order by case when db_account = '001' then 1 else 0 end, id) as sum_
,sum(case when db_account = '001' then qty else 0 - qty end)
over (order by id) as bal
from operations
where
96028 in (db_product_id, kr_product_id)
and '001' in (db_account, kr_account)
order by dir, id
) s
where dir = 1
;

some sales are special and have mark=1 property. How can I map these
sales to particular income operation?

Browse pgsql-sql by date

  From Date Subject
Next Message Suresh Raja 2015-01-23 19:56:53 commit inside a function failing
Previous Message Pavel Stehule 2015-01-11 13:47:46 Re: pg_stat_acvtivity