[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] fifo stock calculation with window function
From:       Dmitry Teslenko <dteslenko () gmail ! com>
Date:       2015-01-16 10:07:22
Message-ID: CAPBNEqO0z2NptKQ8LtjGjgYu=LvgSFN=5gkT6Ca9O3HzPLAahA () mail ! gmail ! com
[Download RAW message or body]

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?


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[prev in list] [next in list] [prev in thread] [next in thread]