tsql - Running Totals with debit credit and previous row SQL Server 2012 -

i having problems in recalculating running totals.

i have situation have duplicate transactions , these must deleted , and initial , closing balance must recalculated based on amount , taking account when isdebit.

my attempt have nested cursors (parent-child) , parent select distinct bookingno , child calculation looks messy , didn't work, didn't post because didn't want confuse things.

i know in sql server 2012 can use (sum on partition by) cannot figure how handle deleted row etc..

below did far

    --create table testing     if object_id(n'testtransaction', 'u') not null  drop table testtransaction      go     create table [testtransaction]     (         [id] [bigint] identity(1,1) not null,         [bookingno] [bigint] not null,         [isdebit] [bit] not null,         [amount] [decimal](18, 2) not null,         [initialbalance] [decimal](18, 2) not null,         [closingbalance] [decimal](18, 2) not null     ) on [primary]     go      insert [testtransaction] ([bookingno], [isdebit], [amount], [initialbalance], [closingbalance])      select 200, 0, 100, 2000,2100 union     select 200, 0, 100, 2100,2200 union      select 200, 1, 150, 2150,2000 union      select 200, 0, 300, 2000,2300 union      select 200, 0, 400, 2300,2700 union      select 200, 0, 250, 2700,2950 union      select 200, 0, 250, 2950,3200  --- end of setup  if object_id('tempdb..#tmptranstodelete') not null drop table #tmptranstodelete go create table #tmptranstodelete (   boookingno bigint,     isdebit bit,     amount decimal(18,2),     initialbalance decimal(18,2),     closingbalance decimal(18,2) )  declare @runnniginitialbalance decimal(18,2),@runnnigclosingbalance decimal(18,2)  insert #tmptranstodelete(boookingno,isdebit,amount,initialbalance,closingbalance) select bookingno,isdebit,amount,initialbalance,closingbalance testtransaction  id in (1,6)  --delete duplicate transaction (just prove point) delete  testtransaction id in (1,6)  -- taking account deleted rows recalculate lot , update table. 

any help? suggestions?

edited results should

    id  bookingno   isdebit amount  initialbalance  closingbalance     2   200         0       100.00  2000.00         2000.00     3   200         1       150.00  2000.00         2150.00     4   200         0       300.00  2150.00         2450.00     5   200         0       400.00  2450.00         2850.00     7   200         0       250.00  2600.00         2850.00 

the runningtotal approach in previous response work if there transactional data accounted initial balance. but, since evidently isn't case, can't delete rows without applying relative difference subsequent rows part of same transaction. moreover, i'm convinced initial sample data wrong, exacerbates confusion. seems me should follows:

select 200, 0, 100, 2000,2100 union select 200, 0, 100, 2100,2200 union  select 200, 1, 150, 2200,2050 union  select 200, 0, 300, 2050,2350 union  select 200, 0, 400, 2350,2750 union  select 200, 0, 250, 2750,3000 union  select 200, 0, 250, 3000,3250 

with rectified, here's how i'd write delete-and-update transaction:

begin tran  declare @tbd table (     id bigint     ,bookingno bigint     ,amount decimal(18,2)     );  delete testtransaction output deleted.id , deleted.bookingno , deleted.amount * iif(deleted.isdebit = 0, 1, -1) amount @tbd id in (1,6);  adj  (     select tt.bookingno, tt.id, sum(tbd.amount) amount     testtransaction tt      join @tbd tbd on tt.bookingno = tbd.bookingno , tbd.id <= tt.id     group tt.bookingno, tt.id     )  update tt set initialbalance -= adj.amount     ,closingbalance -= adj.amount testtransaction tt join adj on tt.bookingno = adj.bookingno , tt.id = adj.id;  commit tran 

which yields final result of:

id  bookingno   isdebit amount  initialbalance  closingbalance 2   200 0   100.00  2000.00 2100.00 3   200 1   150.00  2100.00 1950.00 4   200 0   300.00  1950.00 2250.00 5   200 0   400.00  2250.00 2650.00 7   200 0   250.00  2650.00 2900.00