sql server 2008 - Sum the balance in history table -

i need i'm not able achieve desired output. have used cursor shown below. have 2 tables 1. transaction 2. history. need sum amount in transaction table id , insert in history table under revised_amount column , store amount before sum in original_amount column. below example.

tran_id      id          amount 1066    dhan group       $4,800.00  1327    dhan group       $2,670.00  1329    dhan group       $1,800.00  1330    dhan group       $1,290.00  3953    admin group     0 2650    admin group     $364368.69 2651    admin group     $1604546.97 

desired output in history table

hist_id    id         original_amount      revised_amount 1      dhan group                                $4,800.00  2      dhan group         $4,800.00              $7,470.00  3      dhan group         $7,470.00              $9,270.00  4      dhan group         $9,270.00              $10,560.00   5      admin group                               $0 6      admin group       $0                      $364,368.69  7      admin group       $364,368.89             $1,968,915.86  =============================================================  use [lem] begin declare @proj_rid nvarchar(255), @revised_amount decimal(12,2), @original_amount decimal(12,2)  declare running_total cursor  select [id], [amount] [transactions]  open running_total;  fetch next running_total @proj_rid, @original_amount; while @@fetch_status = 0 begin  set @revised_amount = @revised_amount+ @original_amount;  update history set revised_amount = @revised_amount, original_amount =  @revised_amount - @original_amount [project_id] = @proj_rid  fetch next running_total  @proj_rid, @original_amount; end  close running_total; deallocate running_total; end ======================================================== 

here example of how can achieve this. note need use ordering column transactiondate:

;with cte as(select *, (select sum(amount) transactions                         id = t.id , trandate < t.trandate) original_amount                transactions t) select id, original_amount, amount + isnull(original_amount, 0) revised_amount  cte 

here complete script(but use transaction id ordering column):

declare @t table     (       tran_id int ,       id nvarchar(100) ,       amount money     )  insert  @t values  ( 1066, 'dhan group', 4800.00 ),         ( 1327, 'dhan group', 2670.00 ),         ( 1329, 'dhan group', 1800.00 ),         ( 1330, 'dhan group', 1290.00 ),         ( 2649, 'admin group', 0 ),         ( 2650, 'admin group', 364368.69 ),         ( 2651, 'admin group', 1604546.97 )   ;with cte as(select *, (select sum(amount) @t                         id = t.id , tran_id < t.tran_id) original_amount                @t t) select id, original_amount, amount + isnull(original_amount, 0) revised_amount  cte 

output:

id          original_amount revised_amount dhan group  null            4800.00 dhan group  4800.00         7470.00 dhan group  7470.00         9270.00 dhan group  9270.00         10560.00 admin group null            0.00 admin group 0.00            364368.69 admin group 364368.69       1968915.66