How to find sum of previous rows in every new row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to find sum of previous rows in every new row

Author

Topic

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-02 : 14:10:20

View Ledger
Sr.....Debit.......Credit.....Balance.

1......100.........0..........100.....
2......100.........0..........200.....
3......0...........50.........150.....
4......100.........0..........250.....

We can only enter Sr, Debit and Credit where Balance Column should be automatically calculate in the View.
Note Balance = Debit - Credit + Balance(of last row)
plz help me
Plz note Table Ledger only have Cols ( Sr, Debit and Credit)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 14:24:06

[code]UPDATE t
SET t.Balance=(SELECT SUM(Debit)-SUM(Credit) FROM YourTable WHERE Sr<=t.Sr)
FROM YourTable t[/code]

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-02 : 14:37:25

quote:

Originally posted by visakh16

UPDATE t
SET t.Balance=(SELECT SUM(Debit)-SUM(Credit) FROM YourTable WHERE Sr<=t.Sr)
FROM YourTable t

Thanks Visakh for ur kind cooperation
Plz note
Balance columne is not include in above table
Balance column is only include in The view.
what will be the code in case of view. plz guide me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 14:43:27

[code]CREATE VIEW YourView
AS
SELECT Sr,
Debit,
Credit,
(SELECT SUM(Debit)-SUM(Credit) FROM YourTable WHERE Sr<=t.Sr) AS Balance
FROM YourTable t
GO[/code]

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-02 : 14:55:17

Thanks Visask my problem is solved. thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 15:00:44

You're welcome