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).

StackExchange Database Administrators Q#154592, answer score: 6