I think the simple way is follow, this script run on SQL Server.
Test data:
DECLARE @TestData TABLE (
Id int not null identity(1,1) primary key,
Debit int null,
Credit int null
);
INSERT INTO @TestData (debit, Credit) VALUES (10, 0);
INSERT INTO @TestData (debit, Credit) VALUES (0, 40);
INSERT INTO @TestData (debit, Credit) VALUES (50, 0);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (100, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 110);
The main query:
select * from @TestData
DECLARE @i integer=1;
DECLARE @b integer=0;
declare @temp Table (
Id integer not null,
Debit integer not null,
Credit integer not null,
Balance integer
)
while @i <= (select count(*) from @TestData)
begin
insert into @temp select * , (@b + debit) - credit as b from @TestData a where id = @i
set @b = (select balance from @temp where Id = @i);
set @i = @i+1
end
select * from @temp
And you can see the result as follow !
Input data:
Id Debit Credit
1 10 0
2 0 40
3 50 0
4 0 10
5 0 10
6 100 10
7 0 10
8 0 110
Output:
Id Debit Credit Balance
1 10 0 10
2 0 40 -30
3 50 0 20
4 0 10 10
5 0 10 0
6 100 10 90
7 0 10 80
8 0 110 -30