By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Sample data
declare @Customer_Item table (CustomerId int, Amount money, ItemId int
, ItemPrice money, isActiveStore bit, [Priority] smallint)
insert into @Customer_Item values
(1, 1000, 1, 100, 1, 1), (1, 1000, 2, 200, 1, 2)
,(1, 1000, 3, 300, 0, 3), (2, 5000, 4, 4000, 1, 5)
,(2, 5000, 5, 1100, 1, 7), (2, 5000, 6, 500, 1, 9)
-- ,(2, 5000, 7, 501, 1, 11), (2, 5000, 8, 499, 1, 13)
if object_id('tempdb..#civ') is not null
drop table #civ
select *
into #civ
from @Customer_Item
-- exclude items with price beyond Amount
update #civ
set isActiveStore = 0
where ItemPrice > Amount
-- updating one record each time
set rowcount 1
declare @rc tinyint = 1
while @rc = 1
begin
-- disabling item each time it exceeds the Amount
update citt
set isActiveStore = 0
from
(select isActiveStore
, Amount
, ( sum(ItemPrice * isActiveStore)
over (partition by CustomerId
order by [Priority] )
) as SumPrices
| CustomerId | ItemId | NewItemStatus |
|---|---|---|
| 1 | 1 | Subtracted |
| 1 | 2 | Subtracted |
| 1 | 3 | Skipped |
| 2 | 4 | Subtracted |
| 2 | 5 | Skipped |
| 2 | 6 | Subtracted |
| CustomerID | Amount | NewAmount |
|---|---|---|
| 1 | 1000.0000 | 700.0000 |
| 2 | 5000.0000 | 500.0000 |