Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE inventory ( InventoryID int(11) unsigned PRIMARY KEY AUTO_INCREMENT, > InventoryDate datetime , > Quantity decimal(15,4) , > BBD date NULL , > ItemID int(11) unsigned NULL , > MoneyValuePerUnit decimal(20,4) NULL ); > > <pre> > ✓ > </pre> <!-- --> > INSERT INTO inventory (InventoryDate, Quantity, BBD, ItemID, MoneyValuePerUnit) VALUES > ('2019-12-01 16:23:39', 100, null, 1, 1.23), > ('2019-12-02 16:27:11', 200, null, 1, 1.55), > ('2019-12-03 16:31:58', 100, null, 1, 1.23), > ('2019-12-10 16:23:39', -100, null, 1, 1.23), > ('2019-12-11 16:23:39', -137, null, 1, 1.55), > ('2019-12-12 16:23:39', 100, null, 1, 1.6), > ('2019-12-13 16:23:39', 250, null, 1, 1.6), > ('2019-12-14 16:23:39', -63, null, 1, 1.55), > ('2019-12-15 16:23:39', -40, null, 1, 1.55), > ('2019-12-15 16:23:39', 140, null, 1, 1.65); > > <pre> > ✓ > </pre> <!-- --> > -- a variable with the amount top be sold > set @amount:=280; > > <pre> > ✓ > </pre> <!-- --> > -- query for cte1 > -- calculates total income quantity > -- and balance (income-expense) > SELECT *, > SUM(CASE WHEN Quantity > 0 > THEN Quantity END) OVER (PARTITION BY ItemId > ORDER BY InventoryDate) incomeQuantity, > SUM(Quantity) OVER (PARTITION BY ItemId > ORDER BY InventoryDate) balance > FROM inventory > > <pre> > InventoryID | InventoryDate | Quantity | BBD | ItemID | MoneyValuePerUnit | incomeQuantity | balance > ----------: | :------------------ | --------: | :--- | -----: | ----------------: | -------------: | -------: > 1 | 2019-12-01 16:23:39 | 100.0000 | <em>null</em> | 1 | 1.2300 | 100.0000 | 100.0000 > 2 | 2019-12-02 16:27:11 | 200.0000 | <em>null</em> | 1 | 1.5500 | 300.0000 | 300.0000 > 3 | 2019-12-03 16:31:58 | 100.0000 | <em>null</em> | 1 | 1.2300 | 400.0000 | 400.0000 > 4 | 2019-12-10 16:23:39 | -100.0000 | <em>null</em> | 1 | 1.2300 | 400.0000 | 300.0000 > 5 | 2019-12-11 16:23:39 | -137.0000 | <em>null</em> | 1 | 1.5500 | 400.0000 | 163.0000 > 6 | 2019-12-12 16:23:39 | 100.0000 | <em>null</em> | 1 | 1.6000 | 500.0000 | 263.0000 > 7 | 2019-12-13 16:23:39 | 250.0000 | <em>null</em> | 1 | 1.6000 | 750.0000 | 513.0000 > 8 | 2019-12-14 16:23:39 | -63.0000 | <em>null</em> | 1 | 1.5500 | 750.0000 | 450.0000 > 9 | 2019-12-15 16:23:39 | -40.0000 | <em>null</em> | 1 | 1.5500 | 890.0000 | 550.0000 > 10 | 2019-12-15 16:23:39 | 140.0000 | <em>null</em> | 1 | 1.6500 | 890.0000 | 550.0000 > </pre> <!-- --> > -- query for cte2 > -- calculate total expense > SELECT ItemID, -SUM(Quantity) spent > FROM inventory > WHERE Quantity < 0 > GROUP BY ItemId > > <pre> > ItemID | spent > -----: | -------: > 1 | 340.0000 > </pre> <!-- --> > WITH > cte1 AS ( SELECT *, > SUM(CASE WHEN Quantity > 0 > THEN Quantity END) OVER (PARTITION BY ItemId > ORDER BY InventoryDate) incomeQuantity, > SUM(Quantity) OVER (PARTITION BY ItemId > ORDER BY InventoryDate) balance > FROM inventory ), > cte2 AS ( SELECT ItemID, -SUM(Quantity) spent > FROM inventory > WHERE Quantity < 0 > GROUP BY ItemId ) > SELECT cte1.InventoryID, > cte1.InventoryDate, > cte1.Quantity, > cte1.ItemID, > cte1.MoneyValuePerUnit, > CASE WHEN cte1.incomeQuantity - cte2.spent <= cte1.Quantity > THEN cte1.incomeQuantity - cte2.spent > WHEN @amount + cte2.spent >= cte1.incomeQuantity > THEN Quantity > ELSE GREATEST(@amount + > cte2.spent - > COALESCE(LAG(cte1.incomeQuantity) OVER (PARTITION BY ItemId > ORDER BY InventoryDate), 0), 0) > END expense > FROM cte1 > JOIN cte2 USING (ItemId) > WHERE cte1.incomeQuantity > cte2.spent > AND cte1.Quantity > 0 > ORDER BY cte1.InventoryDate > > <pre> > InventoryID | InventoryDate | Quantity | ItemID | MoneyValuePerUnit | expense > ----------: | :------------------ | -------: | -----: | ----------------: | -------: > 3 | 2019-12-03 16:31:58 | 100.0000 | 1 | 1.2300 | 60.0000 > 6 | 2019-12-12 16:23:39 | 100.0000 | 1 | 1.6000 | 100.0000 > 7 | 2019-12-13 16:23:39 | 250.0000 | 1 | 1.6000 | 120.0000 > 10 | 2019-12-15 16:23:39 | 140.0000 | 1 | 1.6500 | 0.0000 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=be6b969a87c78a3fab5cbb57cec60166)*
back to fiddle