Implementing FIFO (first in first out) in sql

Question

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

Answer 1

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

Answer 2

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.