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_accountcharacter 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
0end, 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?