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.
Author |
Topic |
RoniDxb
Starting Member
28 Posts |
Posted - 2008-08-02 : 07:37:23
|
View ledgerSr.....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 @TABLESELECT 1, 100, 0 UNION ALLSELECT 2, 100, 0 UNION ALLSELECT 3, 0, 50 UNION ALLSELECT 4, 100, 0SELECT t.Sr, t.Debit, t.Credit, b.BalanceFROM @TABLE t CROSS apply ( SELECT Balance = SUM(Debit) - SUM(Credit) FROM @TABLE x WHERE x.Sr <= t.Sr ) bORDER 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] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-02 : 14:47:38
|
quote: Originally posted by RoniDxb View ledgerSr.....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 |
|
|
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 !!!! |
|
|
|
|
|
|
|