clear markdown help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130754 fiddles created (16580 in the last week).

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 );
 hidden batch(es)


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);
 hidden batch(es)


-- a variable with the amount top be sold set @amount:=280;
 hidden batch(es)


-- 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
InventoryID InventoryDate Quantity BBD ItemID MoneyValuePerUnit incomeQuantity balance
1 2019-12-01 16:23:39 100.0000 1 1.2300 100.0000 100.0000
2 2019-12-02 16:27:11 200.0000 1 1.5500 300.0000 300.0000
3 2019-12-03 16:31:58 100.0000 1 1.2300 400.0000 400.0000
4 2019-12-10 16:23:39 -100.0000 1 1.2300 400.0000 300.0000
5 2019-12-11 16:23:39 -137.0000 1 1.5500 400.0000 163.0000
6 2019-12-12 16:23:39 100.0000 1 1.6000 500.0000 263.0000
7 2019-12-13 16:23:39 250.0000 1 1.6000 750.0000 513.0000
8 2019-12-14 16:23:39 -63.0000 1 1.5500 750.0000 450.0000
9 2019-12-15 16:23:39 -40.0000 1 1.5500 890.0000 550.0000
10 2019-12-15 16:23:39 140.0000 1 1.6500 890.0000 550.0000
InventoryID InventoryDate Quantity BBD ItemID MoneyValuePerUnit incomeQuantity balance
1 2019-12-01 16:23:39 100.0000 1 1.2300 100.0000 100.0000
2 2019-12-02 16:27:11 200.0000 1 1.5500 300.0000 300.0000
3 2019-12-03 16:31:58 100.0000 1 1.2300 400.0000 400.0000
4 2019-12-10 16:23:39 -100.0000 1 1.2300 400.0000 300.0000
5 2019-12-11 16:23:39 -137.0000 1 1.5500 400.0000 163.0000
6 2019-12-12 16:23:39 100.0000 1 1.6000 500.0000 263.0000
7 2019-12-13 16:23:39 250.0000 1 1.6000 750.0000 513.0000
8 2019-12-14 16:23:39 -63.0000 1 1.5500 750.0000 450.0000
9 2019-12-15 16:23:39 -40.0000 1 1.5500 890.0000 550.0000
10 2019-12-15 16:23:39 140.0000 1 1.6500 890.0000 550.0000
 hidden batch(es)


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


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
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
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
 hidden batch(es)