c# - T-SQL Find when a amount has reached a set value -

i have table data like:

id  amount  status 1   15.00   paid 2   3.00    paid 3   10.00   awaiting 4   12.00   awaiting 

the system looks @ table see if customer has paid enough subscription. uses table record payments. once day need see if customer has met requirement.

the solution looks nothing above table more complex, issue remain same , can broken down this.

i need find way add amounts up, when amount goes on 20, change data in table follows:

id  amount  status 1   15.00   paid 2   3.00    paid 3   2.00    paid               <= customer has reached payment level 4   12.00   cancelled          <= subsequent payment cancelled 5   8.00    bforward           <= money brought forward 

currently using cursor, performance bad, expected.

does know of better way?

generates desired results. not sure why want update original data (assuming transnational data)

declare @table table (id int,amount money,status varchar(50)) insert @table values (1,15.00,'paid'), (2,3.00,'paid'), (3,10.00,'awaiting'), (4,12.00,'awaiting')  ;with ctebase (     select *           ,sumtotal=sum(amount) on (order id)        @table ), cteextended (     select *           ,forward   = iif(sumtotal>20 , sumtotal-amount<20,sumtotal-20,0)           ,cancelled = iif(sumtotal>20 , sumtotal-amount>20,amount,0)      ctebase ) select id,amount,status='paid' cteextended forward+cancelled=0 union select id,amount=amount-forward,status='paid' cteextended forward>0 union select id,amount,status='cancelled' cteextended cancelled>0 union select id=(select count(*) ctebase)+row_number() on (order id),amount=forward,status='bforward' cteextended forward>0 order id 

returns

id  amount  status 1   15.00   paid 2   3.00    paid 3   2.00    paid 4   12.00   cancelled 5   8.00    bforward