Question 304017 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Implementing FIFO (first in first out) in sql

I have a table of stock

ID Qty DatePurchased Price
11202 4 01/01/2023 3
11202 3 12/06/2023 7
5050 4 11/10/2023 60
11202 4 12/10/2023 5
5050 8 12/12/2023 70

and Item sold table

ID Qty
11202 6

I want to implement the FIFO concept where I can find the total price as follows
I have Quantity purchased in [stock table] on different dates. And I have quantity sold in table [item sold]

let us say I sold 6 quantities for a specific item in my case item "11202" I want to find the total price bymultiplyingy (quantity purchased * price ) // for the first order then multiply the next

4 * 3 = 12 (first order in date 01/01/2023) remaining stock 0

2 * 7 = 14 (second order in date 12/06/2023) remaining stock 1

12 + 14 = 26 total price

I have joined tables which look something like this
joined table
but I don’t know how to add a new column that will include remaining items and the total price

Tags:

2

Answers


  1. 0

    If you want the the total price of each entry:

    SELECT date, qty, sold_qty*price AS total FROM <joined_table> WHERE id = <id> ORDER BY date ASC;

    If you want the total earned money for items:

    SELECT id, MIN(qty), SUM(sold_qty * price) AS total
FROM joined_table
GROUP BY id;

    assuming your quantity always go down, if you want the latest entry’s quantity you should use a subquery.

    You should substitute the code you used to join the table with joined_table

    Login or Signup to reply.
  2. 0

    One approach is to create intervals using the cumulative sums of stock and sales, and then join based on the overlaps:

    WITH stock_intervals AS (
    SELECT *,
        SUM(Qty) OVER w - Qty AS IntervalStart,
        SUM(Qty) OVER w AS IntervalEnd
    FROM stock
    WINDOW w AS (PARTITION BY ID ORDER BY DatePurchased)
),
sales_intervals AS (
    SELECT *,
        SUM(Qty) OVER w - Qty AS IntervalStart,
        SUM(Qty) OVER w AS IntervalEnd
    FROM sales
    WINDOW w AS (PARTITION BY ID ORDER BY Dt)
)
SELECT s.ID, s.DatePurchased, s.Qty, s.Price,
    LEAST(d.IntervalEnd, s.IntervalEnd) - GREATEST(d.IntervalStart, s.IntervalStart) AS tQty,
    s.Price * (LEAST(d.IntervalEnd, s.IntervalEnd) - GREATEST(d.IntervalStart, s.IntervalStart)) AS tPrice
FROM stock_intervals s
JOIN sales_intervals d
  ON s.ID = d.ID
  AND s.IntervalStart < d.IntervalEnd
  AND d.IntervalStart < s.IntervalEnd
ORDER BY s.ID, s.DatePurchased;

    Note: I added the Dt column to sales so there was something to order by for the cumulative sum. Presumably you have a unique id that could be used here.

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search