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