Calculate inventory out row unit price

Question

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:

1. 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
2. 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
3. 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 ((-970*4.01235)+(-10*4.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?

Answer 1

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 < B.to AND B.from < A.to. That will give you intersecting ranges – or, translating into our problem, matching ins and outs. For the example given, the resulting set will look like this:

ins.qty_from  ins.qty_to  outs.qty_from  outs.qty_to
------------  ----------  -------------  -----------
0             2000        0              30
0             2000        30             1030
0             2000        1030           2010
2000          3800        1030           2010

You can see that the first 'in' range repeats three times. That is because the first 'in' row lends its quantity to three 'out' rows. You can also see that the last 'out' range is repeated too, meaning it borrows from both 'in' rows.

Now that ins and outs are successfully matched, the big issue is to correctly determine the quantity that an 'out' row borrows from an 'in' row in case it matches more than one 'in'. There may be variations on the logic to use, here is one:

• if ins.qty_to < outs.qty_to,
  • if ins.qty_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_to
  AND outs.qty_from < ins.qty_to
  CROSS JOIN LATERAL
  (
    SELECT
      CASE
        WHEN ins.qty_to < outs.qty_to THEN
          CASE
            WHEN 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.