# 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:

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?

• This is an interesting development in your previous question, but have you tried using the answer given there to solve this problem? What was your success? Could you include your attempts in this question? Nov 23, 2016 at 12:44
• I took your answer from prev question as template to achive current solution, but i'm little stuck with the idea that there's this `date`. I know that i should separate somehow in and out actions and then do calculations. I'll to write attempt that i have already. Nov 23, 2016 at 13:16
• I've never seen a system or price like this, because most systems are concerned with average: so if you buy 10 units at \$10 (for \$100), and 10 units at \$5 (for \$50) your average price per unit is \$7.5 (\$150/20), and if you sell 10 for \$10, you make \$25. With your system, if you sell 10 for \$10 you make 0. And if you do the same thing again, you double your money! I wonder where this system is useful. May 2, 2018 at 20:34

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.

• Well that's something i've never tried - even close, but i'll start to analyze this whole thing and then i'm going to take it in my context and see if it works in propper way. It will take some time to put all together. I'll let you know if and/or how it worked. Nov 24, 2016 at 6:56