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 2005 Forums
 Transact-SQL (2005)
 how to calculate sum

Author  Topic 

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-02 : 07:37:23
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 values in Debit or Credit column. Balance column should automatically Calculate in the View as new value is added in debit or credit.
where Balance = Debit - Credit + Balance ( of last Previous Row )
plz guide me.
Plz note Table has only columns ( Sr, Debit and Credit)
whereas View has column (Sr, Debit and Credit)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-02 : 09:42:52
[code]DECLARE @TABLE TABLE
(
Sr int,
Debit int,
Credit int
)

INSERT INTO @TABLE
SELECT 1, 100, 0 UNION ALL
SELECT 2, 100, 0 UNION ALL
SELECT 3, 0, 50 UNION ALL
SELECT 4, 100, 0

SELECT t.Sr, t.Debit, t.Credit, b.Balance
FROM @TABLE t
CROSS apply
(
SELECT Balance = SUM(Debit) - SUM(Credit)
FROM @TABLE x
WHERE x.Sr <= t.Sr
) b
ORDER BY t.Sr

/*
Sr Debit Credit Balance
----------- ----------- ----------- -----------
1 100 0 100
2 100 0 200
3 0 50 150
4 100 0 250

(4 row(s) affected)
*/
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 14:47:38
quote:
Originally posted by RoniDxb

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 values in Debit or Credit column. Balance column should automatically Calculate in the View as new value is added in debit or credit.
where Balance = Debit - Credit + Balance ( of last Previous Row )
plz guide me.
Plz note Table has only columns ( Sr, Debit and Credit)
whereas View has column (Sr, Debit and Credit)


you can include the query provided above inside your view provided you're using sql 2005 with compatibility level 90
Go to Top of Page

hipriyankar
Starting Member

1 Post

Posted - 2015-03-27 : 12:48:48
THANKYOU BOSS, IT'S JUST THE MOST EASIEST & SIMPLEST & SUPER PERFORMING LEDGER CODE AVAILABLE ON THE PLANET !!!!
Go to Top of Page
   

- Advertisement -