# Calculate Quantity Based on First in, First Out (FIFO)

I'm trying to get result of quantity based on FIFO, with 2 tables below:

Table Purchase:

``````| PO    | Date         | Quantity | Item |
|-------|--------------|----------|------|
| PO001 | 01-Jan-2016  | 3        | AO21 |
| PO002 | 10-Jan-2016  | 7        | AO21 |
| PO003 | 01-Feb-2016  | 3        | AO21 |
``````

Table Stock:

``````| SO    | Date        | Quantity | Item |
|-------|-------------|----------|------|
| SO001 | 02-Jan-2016 | 2        | AO21 |
| SO002 | 11-Feb-2016 | 8        | AO21 |
| SO003 | 12-Feb-2016 | 6        | AO23 |
``````

I want output to be like this :

``````| SO    | PO    | Quantity |
|-------|-------|----------|
| SO001 | PO001 | 2        |
| SO002 | PO001 | 1        |
| SO002 | PO003 | 7        |
``````

Do you have any idea of query for view this output? Result from calculated SO and PO in row. some more explanation:

Where do the 2, 1, 7 come from in the wanted result?

From `stock` and `purchase`. The first (by date) stock value for item `A021` has 2 and the first purchase (`PO001`) needs 3, so stock sold 2 and we get this row in the result:

``````| SO001 | PO001 | 2        |
``````

We still need 1 more for the purchase and then next stock value is 8. So this purchase is completed and we get 1 (and 7 left in stock):

``````| SO002 | PO001 | 1        |
``````

The next purchase (`PO002`) needs 7 and we have exactly 7 left, so the purchase is completed (and 0 stock left for that item). We get:

``````| SO002 | PO003 | 7        |
``````

Purchase `PO003` needs 3 but there is no stock left, so we get no rows in the result for that purchase.

This is not a trivial problem but it wouldn't be very hard with window functions (and CTEs for readability).

MySQL has not implemented either but lets see how it could be done:

``````WITH
running_purchase AS
( SELECT po, date, quantity, item,
SUM(quantity) OVER (PARTITION BY item
ORDER BY date, po
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS running_total
FROM purchase
),
running_stock AS
( SELECT so, date, quantity, item,
SUM(quantity) OVER (PARTITION BY item
ORDER BY date, so
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS running_total
FROM stock
)
SELECT
s.so, p.po, p.item,
LEAST(p.running_total, s.running_total)
- GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
AS quantity
FROM running_purchase AS p
JOIN running_stock AS s
ON  p.item = s.item
AND s.running_total - s.quantity < p.running_total
AND p.running_total - p.quantity < s.running_total
ORDER BY
p.item, p.date, p.po ;
``````

Tested at SQLFiddle (in Postgres).

Note that MariaDB (which can replace MySQL) has announced that they are working on window functions and CTEs, probably on their next version (10.2). See MariaDB 10.2 Release notes.

For current MySQL versions, it has to be more complex but the logic is the same:

``````SELECT
s.so, p.po, p.item,
LEAST(p.running_total, s.running_total)
- GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
AS quantity
FROM
( SELECT p1.po, p1.date, p1.quantity, p1.item,
SUM(p2.quantity) AS running_total
FROM purchase AS p1
JOIN purchase AS p2
ON  p1.item = p2.item
AND  ( p1.date > p2.date
OR p1.date = p2.date AND p1.po >= p2.po)
GROUP BY p1.item, p1.date, p1.po
) AS p
JOIN
( SELECT s1.so, s1.date, s1.quantity, s1.item,
SUM(s2.quantity) AS running_total
FROM stock AS s1
JOIN stock AS s2
ON  s1.item = s2.item
AND  ( s1.date > s2.date
OR s1.date = s2.date AND s1.so >= s2.so)
GROUP BY s1.item, s1.date, s1.so
) AS s
ON  p.item = s.item
AND s.running_total - s.quantity < p.running_total
AND p.running_total - p.quantity < s.running_total
ORDER BY
p.item, p.date, p.po ;
``````

Tested at SQLFiddle-2 (in MySQL 5.6).

• cant run in mysql workbench? – Mei_R Nov 11 '16 at 13:41
• Yes, see the edit. – ypercubeᵀᴹ Nov 11 '16 at 13:55
• i just tried in my workbench, running correctly,, thank you for your kind answer :) im newbie – Mei_R Nov 11 '16 at 14:27
• @ypercubeᵀᴹ Could you please, advice regarding `running_total`? Do not see such column name in mysql tables. As i see `AS running_total` it is defined after `p.running_total`. So when "call" `p.running_total` the `running_total` does not exist. How do all can work ...? – user2232696 Jun 10 '19 at 9:17
• @user2232696 not sure what you are asking. `running_total` is indeed an alias to a column in the query. What query did you run and what error did you get? – ypercubeᵀᴹ Jun 10 '19 at 10:05