sql - Running Total with minimum balance after modulus -

currently code below running total 50 limit.

now have 50 minimum value remaining. how possibly this. hints helpful. (min balance 50). code in sql 2012, need work sql2008 , above

example :

 - 10        10  - 20        30  - 30        60  - 40        100  --> 50 (min value of 50) 100-50 = 50  - 2         52   - 3         55  - 10        65  - 25        90  - 15        105  --> 55 (min value 50, 105-50 = 55)  - 5         60  declare @table table (id int, listitem int); insert @table values (1, 10); insert @table values (2, 20); insert @table values (3, 30); insert @table values (4, 40); insert @table values (5, 2); insert @table values (6, 3); insert @table values (7, 10); insert @table values (8, 25); insert @table values (9, 15); insert @table values (10, 5); runningtotal ( select id, listitem, sum(listitem) on (order id) % 50 rt     @table) select     rt.id,     rt.listitem,     case when rt.rt < rt2.rt rt.rt + 50 else rt.rt end runningtotal     runningtotal rt     left join runningtotal rt2 on rt2.id = rt.id - 1 order     rt.id; 

edit :

now idea. when try change values, work once. once cumulative value exceeds 350000, deduct 300000, meaning minimum balance 50000.

any hints ?

link : http://sqlfiddle.com/#!6/c9051/1/0

thank you.

the key when want go modulo 50 when number above 100. "remainder" above 50, add 50 to desired output

with runningtotal ( select id, listitem, sum(listitem) on (order id) rt     @table)   select id, listitem, rt, case when rt > 100 rt % 50 + 50 else rt end newrt runningtotal