fifo stock calculation with window function
От | Dmitry Teslenko |
---|---|
Тема | fifo stock calculation with window function |
Дата | |
Msg-id | CAPBNEqO0z2NptKQ8LtjGjgYu=LvgSFN=5gkT6Ca9O3HzPLAahA@mail.gmail.com обсуждение исходный текст |
Список | 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_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?
В списке pgsql-sql по дате отправления: