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 );

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);

-- a variable with the amount top be sold set @amount:=280;

-- 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

-- query for cte2 -- calculate total expense SELECT ItemID, -SUM(Quantity) spent FROM inventory WHERE Quantity < 0 GROUP BY ItemId

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