By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Customer (
"CustomerId" INTEGER,
"Amount" INTEGER
);
INSERT INTO Customer
("CustomerId", "Amount")
VALUES
('1', '1000'),
('2', '5000');
CREATE TABLE Item (
"CustomerId" INTEGER,
"ItemId" INTEGER,
"ItemPrice" INTEGER,
"IsActiveStore" INTEGER,
"Priority" INTEGER,
"ItemStatus" varchar(30)
);
INSERT INTO Item
("CustomerId", "ItemId", "ItemPrice", "IsActiveStore", "Priority")
VALUES
('1', '1', '100', '1', '1'),
('1', '2', '200', '1', '2'),
('1', '3', '300', '0', '3'),
('2', '4', '1000', '1', '1'),
('2', '5', '4000', '1', '2'),
('2', '6', '500', '1', '3');
8 rows affected
WITH cte AS (
SELECT
i.*,
c.Amount,
SUM(i.ItemPrice) OVER (PARTITION BY i.CustomerId, i.IsActiveStore
ORDER BY I.Priority ROWS UNBOUNDED PRECEDING) AS CumeSum
FROM Item i
JOIN Customer c ON c.CustomerId = i.CustomerId
)
UPDATE cte
SET ItemStatus = IIF(IsActiveStore = 0 OR CumeSum > Amount, 'skipped', 'subtracted');
UPDATE c
SET Amount -= ISNULL(i.Total, 0)
FROM Customer c
CROSS APPLY (
SELECT SUM(i.ItemPrice) AS Total
FROM Item i
WHERE c.CustomerId = i.CustomerId
AND i.ItemStatus = 'subtracted'
GROUP BY
i.CustomerId
) i;
8 rows affected
SELECT * FROM Customer;
SELECT * FROM Item;
| CustomerId | Amount |
|---|---|
| 1 | 700 |
| 2 | 0 |
| CustomerId | ItemId | ItemPrice | IsActiveStore | Priority | ItemStatus |
|---|---|---|---|---|---|
| 1 | 1 | 100 | 1 | 1 | subtracted |
| 1 | 2 | 200 | 1 | 2 | subtracted |
| 1 | 3 | 300 | 0 | 3 | skipped |
| 2 | 4 | 1000 | 1 | 1 | subtracted |
| 2 | 5 | 4000 | 1 | 2 | subtracted |
| 2 | 6 | 500 | 1 | 3 | skipped |