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

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?

• What version of MySQL? Commented Jul 8, 2019 at 23:12
• You MUST guarantee that `Date` field is unique (by proper unique index). If not the result became non-deterministic. Commented Jul 9, 2019 at 4:34
• @RickJames MySQL 5.6
– Joel
Commented Jul 9, 2019 at 21:07
• Akina, the Date on which table? actually, both tables have ID's I just used a ready example for illustration. @ypercubeᵀᴹ gave great advice here dba.stackexchange.com/a/154960/183959 but I am not sure how to add cost in as mentioned.
– Joel
Commented Jul 9, 2019 at 21:10
• Consider upgrading to 8.0 and using the "wnidowing" functions. Commented Jul 9, 2019 at 21:22

Maybe this is:

``````select Item, sum(Quantity) Quantity, sum(Cost) Cost,
(SELECT SUM(adjustment_qty) FROM item_inventories i WHERE i.item=o.Item) adjustment_qty
from purchase_order_items o
group by Item;
``````

I want to apologize up front. I don't work with `MySQL` but maybe this `SQL Server` answer will help give you some guidance.

I think what you really want is a `Remaining_Inventory` column added onto each row of `purchase_order_items` (or a separate table). Then each transaction can update the appropriate row in your `FIFO` methodology. Then your inventory cost report is simply something like:

``````SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
SELECT Item AS 'Item',
(Cost * Remaining_Inventory) AS 'Cost_Of_Remaining_Inventory'
FROM Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item
``````

You would then need to fill this table out with your initial data, and I think I have a `CURSOR` which will do what you need. Again written for `SQL Server`.

First I recreated your tables with `Table Variables` just so i could test everything out:

``````DECLARE @Purchase_Order_Items TABLE
(
PO VarChar(5),
[Date] DATETIME,
Quantity INT,
Item VarChar(4),
Cost DECIMAL(10,2)
)

DECLARE @Item_Inventories TABLE
(
ID VarChar(5),
[Date] DATETIME,
Item VarChar(4)
)

INSERT INTO @Purchase_Order_Items (PO, [Date], Quantity, Item, Cost)
VALUES('PO001', '2019-01-01', 1, 'AO21', 12.50),
('PO002', '2019-01-02', 3, 'AO21', 10.99),
('PO003', '2019-01-09', 2, 'AO21', 12.00)

INSERT INTO @Item_Inventories (ID, [Date], Adjustment_Qty, Item)
VALUES ('1O001', '2019-01-01', 1, 'AO21'),
('1O002', '2019-01-02', 3, 'AO21'),
('1O003', '2019-01-04', -1, 'AO21'),
('1O004', '2019-01-05', -1, 'AO21'),
('1O005', '2019-01-09', 2, 'AO21')
``````

Then I created a "Tracker" table for keeping track of the remaining inventory for a given PO as we work through the cursor.

``````DECLARE @Purchase_Order_Items_Tracker TABLE
(
PO VarChar(5),
[Date] DATETIME,
Quantity INT,
Item VarChar(4),
Cost DECIMAL(10,2),
Inventory_Remaining INT
)
``````

Then running the below code to populate said table and then query off it should give you what you are looking for

``````DECLARE @Date DATETIME,
@Item VarChar(4),
@Item_Inventories_PO_Transaction VarChar(5)

DECLARE Inventory_Cursor CURSOR FOR
--getting the absolute value of Adjustment_Qty so that we can only work with positive number
---The WHERE condition makes sure we are only working with records where the inventory was subtracted from, not added to
FROM @Item_Inventories
WHERE Adjustment_Qty < 0 --That way we are only looking at removing of inventory
--Not logs where inventory was added

OPEN Inventory_Cursor
FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item

WHILE(@@FETCH_STATUS = 0)
BEGIN
--Load in any new inventory
INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
SELECT
I.PO,
I.[Date],
I.Quantity,
I.Item,
I.Cost,
I.Quantity
FROM @Purchase_Order_Items I
LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
ON I.PO = T.PO
WHERE I.Item = @Item --Transaction is for our item in scope
AND I.[Date] <= @Date --Purchase order recieved at or before this inventory change
AND T.PO IS NULL --Record not already inserted into the tracker

--Then Subtract from inventory
BEGIN
SET @Adjustment_Qty_Transaction = NULL
SET @Item_Inventories_PO_Transaction = NULL

--You will need some kind of error handling for if there are no records found
--which means at the time of trying to take out inventory
--our records show that there was no inventory for that item
SELECT TOP(1)
@Item_Inventories_PO_Transaction = PO
FROM @Purchase_Order_Items_Tracker
WHERE Item = @Item
AND Inventory_Remaining > 0
AND [Date] <= @Date
ORDER BY [Date] DESC

BEGIN
SET @Adjustment_Qty = 0
END
ELSE
BEGIN
SET @Adjustment_Qty_Transaction = 0
END

UPDATE @Purchase_Order_Items_Tracker
SET Inventory_Remaining = @Adjustment_Qty_Transaction
WHERE @Item_Inventories_PO_Transaction = PO
END

FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item
END
CLOSE Inventory_Cursor
DEALLOCATE Inventory_Cursor

--Then make sure that we have all other inventory additions added
--In case we got new inventory but have not had inventory taken out since it was added

INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
SELECT
I.PO,
I.[Date],
I.Quantity,
I.Item,
I.Cost,
I.Quantity
FROM @Purchase_Order_Items I
LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
ON I.PO = T.PO
WHERE T.PO IS NULL --Record not already inserted into the tracker

SELECT *
FROM @Purchase_Order_Items_Tracker

SELECT Item AS 'Item',
[Date],
(Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
FROM @Purchase_Order_Items_Tracker

SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
SELECT Item AS 'Item',
(Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
FROM @Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item
``````

The 3 final `SELECT` queries return the below 3 Tables:

``````+-------+-------------------------+----------+------+-------+---------------------+
|  PO   |          Date           | Quantity | Item | Cost  | Inventory_Remaining |
+-------+-------------------------+----------+------+-------+---------------------+
| PO001 | 2019-01-01 00:00:00.000 |        1 | AO21 | 12.50 |                   0 |
| PO002 | 2019-01-02 00:00:00.000 |        3 | AO21 | 10.99 |                   2 |
| PO003 | 2019-01-09 00:00:00.000 |        2 | AO21 | 12.00 |                   2 |
+-------+-------------------------+----------+------+-------+---------------------+

+------+-------------------------+-----------------------------+
| Item |          Date           | Cost_Of_Remaining_Inventory |
+------+-------------------------+-----------------------------+
| AO21 | 2019-01-01 00:00:00.000 |                        0.00 |
| AO21 | 2019-01-02 00:00:00.000 |                       21.98 |
| AO21 | 2019-01-09 00:00:00.000 |                       24.00 |
+------+-------------------------+-----------------------------+

+------+------------------+
| Item | Sum_Of_Inventory |
+------+------------------+
| AO21 |            45.98 |
+------+------------------+
``````

Again all of this is based around `SQL Server` but it should at least give you a framework to build off of. And I think in the real implementation you want a `Remaining_Inventory` `Column` (or a separate table) which keeps track of what is left on that `PO`

I ended up using the following operation.

Either in 2 queries and using php to run it or in a single MySQL query.

2 Queries:

`SELECT SUM(adjustment_qty) FROM item_inventories WHERE item='AO21'`

I then take the result, let's say 34 and I run the following query

```SELECT SUM(cost) FROM (select cost from purchase_order_receipt_items WHERE item='AO21' ORDER BY id DESC LIMIT 34) i```

Or, we can run the same in a single query.

``````SET @sql = CONCAT('SELECT SUM(cost) FROM
(select cost from purchase_order_receipt_items WHERE item='AO21'
ORDER BY id DESC
LIMIT ', (SELECT SUM(adjustment_qty) FROM item_inventories i WHERE item='AO21'), ') i');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
``````

This gives you a perfect cost of goods report based on FIFO