2

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?

  • 1
    What version of MySQL? – Rick James Jul 8 at 23:12
  • You MUST guarantee that Date field is unique (by proper unique index). If not the result became non-deterministic. – Akina Jul 9 at 4:34
  • @RickJames MySQL 5.6 – Joel Jul 9 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 Jul 9 at 21:10
  • Consider upgrading to 8.0 and using the "wnidowing" functions. – Rick James Jul 9 at 21:22
0

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;
0

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,
    Adjustment_Qty INT,
    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, 
        @Adjustment_Qty INT, 
        @Item VarChar(4),
        @Adjustment_Qty_Transaction INT,
        @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
SELECT [Date], ABS(Adjustment_Qty) AS Adjustment_Qty, Item
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
    WHILE(@Adjustment_Qty > 0)
    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) 
            @Adjustment_Qty_Transaction = Inventory_Remaining,
            @Item_Inventories_PO_Transaction = PO
        FROM @Purchase_Order_Items_Tracker
        WHERE Item = @Item
        AND Inventory_Remaining > 0
        AND [Date] <= @Date
        ORDER BY [Date] DESC

        IF(@Adjustment_Qty <= @Adjustment_Qty_Transaction)
        BEGIN
            SET @Adjustment_Qty_Transaction = @Adjustment_Qty_Transaction - @Adjustment_Qty
            SET @Adjustment_Qty = 0
        END
        ELSE
        BEGIN
            SET @Adjustment_Qty = @Adjustment_Qty - @Adjustment_Qty_Transaction
            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

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.