#StackBounty: #mysql Calculate Inventory Costing Based on First in, First Out (FIFO)

Bounty: 50

I am trying to get the cost of inventory based on FIFO.

I saw a similar question but it does not look for cost of inventory and tables seem to be structured differently.

Using MySQL I have a table purchase_order_items, purchase_order_receipt_items and item_inventories the purchase_order_receipt_items table gets updated with every item that is received and for purchasing control, what was ordered and what was received.

The item_inventories table gets updated with a new line item for every inventory movement in or out and we calculate the current inventory using a simple SUM of all in and out quantity.

What I am missing is the cost of the current inventory. The cost of the same item varies and we don’t have a cost added in the item_inventories table.

Table purchase_order_items:

| PO    | Date         | Quantity | Item | Cost  | 
|-------|--------------|----------|------|-------|
| PO001 | 01-Jan-2019  | 1        | AO21 | 12.50 | 
| PO002 | 02-Jan-2019  | 3        | AO21 | 10.99 |
| PO003 | 09-Jan-2019  | 2        | AO21 | 12.00 |

Table item_inventories

| ID    | Date         | adjustment_qty | Item |
|-------|--------------|----------------|------|
| 1O001 | 01-Jan-2019  | 1              | AO21 |
| 1O002 | 02-Jan-2019  | 3              | AO21 |
| 1O003 | 04-Jan-2019  | -1             | AO21 |
| 1O004 | 05-Jan-2019  | -1             | AO21 |
| 1O005 | 09-Jan-2019  | 2              | AO21 |

I currently calculate the item_inventories and get a total of 4 SELECT SUM(adjustment_qty) FROM item_inventories WHERE item='AO21' but that does not give me the cost of the inventory.

How do I join the purchase_order_items table to get the cost and qty in a SUM query of the item_inventories table?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.