I would like to ask how to use the FIFO method with MYSQL8 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 | 1300 |
| 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.
New contributor
user1924932 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.