How to calculate balance on the basis of Previous

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

General SQL Server Forums

New to SQL Server Programming

How to calculate balance on the basis of Previous

Author

Topic

imrankasuri
Starting Member

1 Post

Posted - 2011-10-05 : 02:47:02

Hi All,
i am currently facing an issue and hope that someone will help me.
here is the scenario.
I have a simple table.
Member Table
================

Member_id Name Opening_Balance Balance_Type
1 David 5000 Debit

Transaction Table
=================
Transaction_id Transaction_date Member_id Description Debit Credit
1 10/01/2011 1 Cash 500 0
2 10/02/2011 1 Cash 0 1000
3 10/03/2011 1 Cash 500 0
4 10/04/2011 1 Cash 0 8000

Now what i need to show the Balance after each entry and its Type. The final result will be look like this.

Transaction Details View
========================
Transaction_id Transaction_date Member_id Description Debit Credit Balance Balance_Type
1 10/01/2011 1 Cash 500 0 5500 Debit
2 10/02/2011 1 Cash 0 1000 4500 Debit
3 10/03/2011 1 Cash 500 0 5000 Debit
4 10/04/2011 1 Cash 0 8000 3000 Credit

How can i create this view? the final balance is prepared by using the opening balance from the members table and then each calculation is made by the balance of previous record to the next and balance type is changed accordingly.
I hope that some expert will help.

Ali Imran

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-05 : 02:51:30

are you using SQL Server 2005 / 2008 ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 03:01:42

[code]
SELECT t.Transaction_id,
t.Transaction_date,
t.Member_id,
t.Description,
t.Debit,
t.Credit,
t1.Opening_Balance + CASE WHEN t1.Balance_Type = 'Debit' THEN t2.DebitTotal ELSE -t2.DebitTotal END
+ CASE WHEN t1.Balance_Type = 'Debit' THEN -t2.CreditTotal ELSE t2.CreditTotal END AS Balance,
t1.Balance_Type
FROM Transaction t
CROSS APPLY (SELECT Opening_Balance, Balance_Type
FROM Member
WHERE Member_id=t.Member_id)t1
CROSS APPLY(SELECT SUM(Debit) AS DebitTotal,SUM(Credit) AS CreditTotal
FROM Transaction
WHERE Member_id=t.Member_id
AND Transaction_date <=t.Transaction_date)t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources