Mysql - How to calculate stock with price using FIFO method

user1924932
August 13, 2024
103 views
0 votes
2 Answers

I would like to ask how to use the FIFO method with MySQL 8 to generate the expected result as follow.

product_id	type	quantity	price	operation_date
1	PURCHASE	10	100	2024-08-01
2	PURCHASE	25	80	2024-08-02
2	SALE	20	140	2024-08-03
1	SALE	7	120	2024-08-03
2	PURCHASE	20	90	2024-08-04
3	PURCHASE	40	50	2024-08-05
3	PURCHASE	20	40	2024-08-06
3	PURCHASE	20	20	2024-08-07
2	SALE	20	160	2024-08-07
3	SALE	50	150	2024-08-07
3	SALE	20	160	2024-08-08
1	PURCHASE	10	80	2024-08-09

I need to calculate stock value for each product.

Expected Output

product_id	Stock Value
1	1100
2	450
3	200

To accurately calculate the current stock value, we need to use the First-In-First-Out (FIFO) method. The FIFO method means that the items bought first are sold first, so the remaining stock should be valued at the most recent purchase prices.

I have tried many queries but I am not getting what I am looking for.

Stock Value Calculation Example for Product Id 1 (manual calculation)

Purchase 10 qty at price 100 on 2024-08-01 so that stock value is 1000
after 2024-08-01, I sold 7 qty at 120 so now my stock value will be 300 as the purchase price was 100.
On 2024-08-09 I purchased another 10 qty at 80.
Now the final stock value for me is:
3  qty @ 100 = 300 (left from a previous purchase)
10 qty @ 80  = 800 
------------------- 
13 qty      = 1100 stock value

Stock Value Calculation Example for Product Id 2 (manual calculation)

Purchase 25 qty at price 80 
Sell     20 qty at 
---------------------------
          5 qty remaining from first purchase at 80 price
Purchase 20 qty at price 90
---------------------------
   Total 25 qty (5 qty @ 80 price and 20 qty @ 90 price)
Sell     20 qty (using fifo method 5 deduct from first purchase and 15 deduct from second purchase)
---------------------------
Remaining 5 qty @ 90 price 
So, the stock value is 5 * 90 = 450

Stock Value Calculation Example for Product Id 3 (manual calculation)

Purchase  40 qty at price 50
Purchase  20 qty at price 40
Purchase  20 qty at price 20
----------------------------
-         50 qty (sell)
----------------------------
Remaining 30 qty (using FIFO 10 qty left at 40 price and 20 left at 20 price
----------------------------
-         20 qty (sell)
----------------------------
Remaining 10 qty (using FIFO 10 qty left at 20 price)
So, stock value is 10 qty * 20 price = 200

NOTE: IF CHANGING THE STRUCTURE OF TABLE CAN HELP IN SOLVING THIS
PROBLEM THEN ALSO SUGGEST.

Tags: mysql

Answers

Here is the query to calculate the stock value for each product using FIFO:

The recursive common table expression (inventory_cte) processes purchases and sales. For each sale, it calculates the remaining quantities and the cost of the remaining inventory.

Summarize the stock values by summing up the total cost for each product where remaining quantity is greater than zero.

dbfiddle

WITH RECURSIVE inventory_cte AS (
    SELECT 
        product_id, 
        type, 
        quantity, 
        price, 
        operation_date,
        quantity AS remaining_quantity,
        price * quantity AS total_cost
    FROM inventory
    WHERE type = 'PURCHASE'
    
    UNION ALL

    SELECT 
        i.product_id, 
        i.type, 
        i.quantity, 
        i.price, 
        i.operation_date,
        CASE 
            WHEN inv.remaining_quantity >= i.quantity THEN inv.remaining_quantity - i.quantity
            ELSE 0 
        END AS remaining_quantity,
        CASE 
            WHEN inv.remaining_quantity >= i.quantity THEN inv.total_cost - (i.quantity * inv.price)
            ELSE inv.total_cost 
        END AS total_cost
    FROM inventory_cte inv
    JOIN inventory i ON inv.product_id = i.product_id
    WHERE i.type = 'SALE' AND i.quantity <= inv.remaining_quantity
)

SELECT 
    product_id,
    SUM(total_cost) AS stock_value
FROM inventory_cte
WHERE remaining_quantity > 0
GROUP BY product_id;

So the best approach might be using CTE_Operations: creating a CTE (CTE_Operations) that adjusts the quantity based on whether the operation is a SALE or PURCHASE. For sales, the quantity is negated to reflect stock reduction. See docs for reference

CTE_RunningStock: CTE calculates the running stock for each product. usually with a window function to accumulate the stock after each transaction (SUM(adjusted_quantity) OVER (...)).

Finally CTE_StockAfterSales calculates the remaining stock after sales have been applied.

Here is a code snippet:

WITH CTE_Transactions AS (
    SELECT 
        product_id,
        type,
        quantity,
        price,
        operation_date,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY operation_date) AS rn
    FROM test
),
CTE_Sales AS (
    SELECT 
        product_id, 
        quantity,
        operation_date,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY operation_date) AS rn
    FROM CTE_Transactions
    WHERE type = 'SALE'
),
CTE_Purchases AS (
    SELECT 
        product_id,
        quantity,
        price,
        operation_date,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY operation_date) AS rn
    FROM CTE_Transactions
    WHERE type = 'PURCHASE'
),
CTE_StockAfterSales AS (
    SELECT
        p.product_id,
        p.quantity AS purchase_quantity,
        p.price,
        p.operation_date,
        s.quantity AS sale_quantity,
        p.quantity - COALESCE(s.quantity, 0) AS remaining_quantity,
        p.price * (p.quantity - COALESCE(s.quantity, 0)) AS stock_value
    FROM 
        CTE_Purchases p
    LEFT JOIN 
        CTE_Sales s
    ON 
        p.product_id = s.product_id AND p.rn = s.rn
    WHERE 
        p.quantity - COALESCE(s.quantity, 0) > 0
)
SELECT 
    product_id,
    SUM(stock_value) AS stock_value
FROM 
    CTE_StockAfterSales
GROUP BY 
    product_id
ORDER BY 
    product_id;

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – How to calculate stock with price using FIFO method

Answers