6

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.

6

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

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.