Calculate inventory out row unit price

Currently I have data like this,

CREATE TABLE foo (id, qty, unit_price, date, action) AS
    VALUES
    (1,  2000, 4.01235, '2015-10-10'::timestamp, 'in'),
    (2,   -30, NULL   , '2015-10-11'::timestamp, 'out'),
    (3,  1800, 4.9    , '2015-10-25'::timestamp, 'in'),
    (4, -1000, NULL   , '2015-11-12'::timestamp, 'out'),
    (5,  -980, NULL   , '2015-11-20'::timestamp, 'out');

I need to get calculated avg price for outgoing rows so that result look like this.

id | qty | unit_price | date | action
----------------------------------------------
1 | 2000 | 4.01235 | 2015-10-10 | in
2 | -30 | 4.01235 | 2015-10-11 | out
3 | 1800 | 4.9 | 2015-10-25 | in
4 | -1000 | 4.01235 | 2015-11-12 | out
5 | -980 | 4.02141 | 2015-11-20 | out

unit_price is calculated for outgoing in this way:

• for id=2, since there's 2000 unused from only one income the result unit_price is the same as first income row id=1

• for id=4, there's still 1970 unused and it's bigger than needed (-1000) - so result unit_price is still the same as first income row id=1

• for id=5, there's left only 970 with the first in action, but theres more with in action id=3 1800. So basically i could use -970 with price 4.01235 and -10 with price 4.9. So as result i get the avg price for this row like this ((-9704.01235)+(-104.9)) / -980 = 4.02141 when rounding to 5 decimals.

All the price calculations have to consider date column. Basically it is FIFO calculation logic.

Does anyone have idea how to do this kind of calculation?

There appear to be two key issues here.

One is finding a way to match an 'out' quantity with all relevant 'in' quantities. In your case, row 5 needs to be matched with rows 1 and 3 because it uses both rows' quantities, as follows from the order of ins and outs.

You could try using a method like this. Take the two subsets of the table, ins and outs, and calculate two running totals for each, one including the current value (call it qty_from) and the other excluding it (i.e. the running total of all preceding values). Let us call the former qty_to and the latter qty_from. You will get these results for each subset:

-
for ins:

qty_from  qty_to
--------  ------
0         2000
2000      3800

-
for outs:

qty_from  qty_to
--------  ------
0         30
30        1030
1030      2010

Now join the two subsets using a well-known range matching method: A.from outs.qty_from,



• take the current ins.qty

• otherwise,

• take ins.qty_to - outs.qty_from;

• otherwise,

• if outs.qty_from > ins.qty_from,






• take the current outs.qty

• otherwise,

• take outs.qty_to - ins.qty_from.




It only remains to apply your formula to the obtained values, which is essentially SUM(ins.unit_price * borrowed_qty) / outs.qty`.

Translating all the above into SQL, we can get a query like this:

SELECT
  outs.id,
  outs.qty,
  ROUND(SUM(x.borrowed_qty * ins.unit_price) / outs.qty, 5) AS unit_price,
  outs.date,
  outs.action
FROM
  (
    SELECT
      *,
      COALESCE(SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'in'
  ) AS ins
  INNER JOIN
  (
    SELECT
      *,
      COALESCE(SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'out'
  ) AS outs
  ON  ins.qty_from  outs.qty_from THEN -ins.qty
            ELSE -(ins.qty_to - outs.qty_from)
          END
        ELSE
          CASE
            WHEN outs.qty_from > ins.qty_from THEN outs.qty
            ELSE -(outs.qty_to - ins.qty_from)
          END
      END
  ) AS x (borrowed_qty)
GROUP BY
  outs.id,
  outs.qty,
  outs.date,
  outs.action
;

A demo of this solution can be found at Rextester.

StackExchange Database Administrators Q#156121, answer score: 6