reporting services - Nested Cursor based update -

so ssrs report i'm using temp tables. have 2 tables, 1 transactions i'm using pull account , amounts. second amortized delinquency information i'm trying adjust based on transaction i'm running syntax issues. case statements not allowed used cursors or updates?

--example transaction:account 123456 principal 500.00 interest 250.00  delinquent 5 months of 200 principal each month, transaction had  principal amount of 500, first 2 left alone, third reduced 100 match total transaction amount, leaving remaining amount of 0  --delinquent data -- 11/2015 prin 200 int 80 -> prin 0   int 0 (running total  prin 500 int 250) -- 10/2015 prin 200 int 80 -> prin 0   int 10 (running total  prin 500 int 250) --  9/2015 prin 200 int 80 -> prin 100 int 80 (running total  prin 500 int 240) --  8/2015 prin 200 int 80 -> prin 200 int 80 (running total  prin 400 int 160) --  7/2015 prin 200 int 80 -> prin 200 int 80 (running total  prin 200 int 80) <- process starts oldest , goes up. 

this requires use of cursor because cannot process in set has start oldest row, adjust, find remaining total, carry on adjusting next row. if transaction amount reached, remaining rows zeroed out. i'm returning remaining amounts transaction table.

declare  trancursor cursor forward_only  select loannumber, principalcollected, interestcollected, servicefee, payoffprincipal,payoffinterest,payoffservicefee #transfinal  update of payoffprincipal,payoffinterest,payoffservicefee   open trancursor; fetch next trancursor @tranloan, @tranprin, @tranint, @transerv, @poprin,@point,@poserv while (@@fetch_status = 0)  begin     --process individual loan's transaction going through each set of amortized amounts starting oldest , reducing excess of transaction amounts zero.      --eg. delinquent 3 months of 200 principal each month, transaction had principal amount of 500, first 2 left alone, third reduced 100 match total transaction amount     --transaction principal 500 interest 250     --delinquent data     -- 11/2015 prin 200 int 80 -> prin 0   int 0 (running total  prin 500 int 250)     -- 10/2015 prin 200 int 80 -> prin 0   int 10 (running total  prin 500 int 250)     --  9/2015 prin 200 int 80 -> prin 100 int 80 (running total  prin 500 int 240)     --  8/2015 prin 200 int 80 -> prin 200 int 80 (running total  prin 400 int 160)     --  7/2015 prin 200 int 80 -> prin 200 int 80 (running total  prin 200 int 80) <- process starts oldest , goes up.     declare  delqcursor  cursor forward_only      select loannumber,delqprin ,delqint  ,delqservfee #dq          loannumber = @tranloan         order paidtodate asc         update of delqprin, delqint, delqservfee;         open delqcursor; ----------------------------------------------------------------------------------------------------------- -- processing individual row of delinquent data ----------------------------------------------------------------------------------------------------------- fetch next delqcursor  @delqprin, @delqint, @delqserv, @delqptd --, @poprin,@point,@poserv while (@@fetch_status = 0)      begin               case when @tranprin = 0 set @delqprin = 0 -- syntax error on case                 when @tranprin >0 , @tranprin > @delqprin -- syntax error on when                     set @tranprin = @tranprin - @delqprin                     set @colprin = @colprin + @delqprin                 when @tranprin >0 , @tranprin < @delqprin                      set @colprin = @colprin + @tranprin                     set @tranprin = 0                      set @delqprin = @delqprin -@tranprin             end              case when @tranint = 0 set @delqint = 0                 when @tranint >0 , @tranint > @delqint                      set @tranint = @tranint - @delqint                     set @colint = colint + @delqint                 when  @tranint >0 , @tranint < @delqint                      set @colint = @colint + @tranint                     set @tranint = 0                      set @delqint = @delqint -@tranint             end              case when @transerv = 0 set @delqservfee = 0                 when  @transerv >0 , @transerv> @delqserv                      set @transerv = @transerv - @delqserv                     set @colserv = colserv + @delqserv                 when  @transerv >0 , @transerv < @delqserv                      set @colserv = @colserv + @transerv                     set @transerv = 0                      set @delqserv = @delqserv -@transerv             end  fetch next delqcursor  @delqprin, @delqint, @delqserv, @delqptd  end ----------------------------------------------------------------------------------- --all rows of delinquent data single loan have been processed. update payoff columns ---------------------------------------------------------------------------------- set @poprin =  @colprin set @point =  @colint set @poserv =  @colserv --todo finish update statement outside loop update transaction table close delqcursor-- finished delinquent data loan. close cursor fetch next trancursor @tranloan, @tranprin, @tranint, @transerv, @poprin,@point,@poserv --start processing next loan end   close trancursor deallocate delqcursor deallocate trancursor 

i'd appreciate insight can figure out why case when statements giving me errors. see nothing in msdn syntax prevent case logic working.

the way have coded case statements not work. case logic needs happen within select, or set. yours out there, , set in middle, after then.

you need change logic each case have in code.

if @tranprin = 0      set @delqprin = 0   if @tranprin >0 , @tranprin > @delqprin begin     set @tranprin = @tranprin - @delqprin     set @colprin = @colprin + @delqprin end  if @tranprin > 0 , @tranprin < @delqprin begin     set @colprin = @colprin + @tranprin     set @tranprin = 0      set @delqprin = @delqprin -@tranprin end 

case (transact sql) reference