Calculate balance - SQL Server Forums

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)

Calculate balance

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-12 : 04:00:07

I have some problem with balance calculation(I have posted a similar query like this one week before ). I have a table with SchoolID(nvarchar),
RegNo(nvarchar),Period(Nvarchar),Feedesc(nvarchar),Debit(money),Credit(money).

No I have to calculate the balance.

If I will pass the values (condition) like (Period >=CONVERT(datetime,'11/12/2005')
and Period<=CONVERT(datetime,'14/12/2005')) and schoolid='ab0' and regno='333333'

I should get the result like (I have to retrieve schoolid,regno,debit,credit,balance)

AB0 333333 12/12/2005 Sports 12/12/2005 12 0 12.00
AB0 333333 13/12/2005 First Term Exam 13/12/2005 14 0 26.00
AB0 333333 13/12/2005 Swimming 13/12/2005 10 0 36.00
AB0 333333 13/12/2005 Misc 13/12/2005 0 10 26.00
AB0 333333 14/12/2005 Second Term Exam14/12/2005 0 10 16.00

Sample data from which I have typed the output is

SchoolID RegNo Period FeeDesc Debit Credit

AB0 333333 12/12/2005 Sports 12 0
AB0 333333 13/12/2005 First Term Exam 14 0
AB0 333333 14/12/2005 Second Term Exam 0 10
AB0 333333 15/12/2005 Misc. 0 16
AB0 333333 13/12/2005 Swimming 10 0
AB0 222222 01/01/2006 Monthly Test Fee 88 0
AB0 222222 01/10/2005 Anu. day 65 0
AB0 111111 02/10/2006 Mess Fee 0 777
AB0 111111 03/02/2005 Cel. Fee 0 8
AB1 777777 03/02/2005 Month Cel Fee 88 888
AB0 333333 13/12/2005 Misc 0 10

Please help

Thanks
Ceema

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-12 : 04:04:28

That means

Debit-credit should be calculeted for each row(like..from the result,I am explaining)

debit credit balance
12 0 12
14 0 26
10 0 36
0 10 26
0 10 16

Thank you
ceema

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 05:15:09

It will be easier if you could do the running total in your front end or reporting tool

----------------------------------
'KH'
everything that has a beginning has an end

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-12 : 05:24:36

Hello Khan,

So you think it's difficult to do it in stored procedure?

Thank you
Ceema

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 06:22:38

Nope. It jsut that it is much easier to do this at the Front End or Reporting Tool

select 	*, (select sum(Debit - Credit) from table1 x
		where	x.SchoolID	= t.SchoolID
		and	x.RegNo		= t.RegNo
		and	(
				x.Period	< t.Period
			or	(x.Period	= t.Period	and	x.FeeDesc	<= t.FeeDesc)
			)
		)
from 	table1 t
where	t.RegNo	= '333333'
order by SchoolID, RegNo, Period, FeeDesc

----------------------------------
'KH'
everything that has a beginning has an end

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-12 : 06:42:12

Hello Khan,

Thank you so much, this time also you saved me.

Thank you so much,

Ceema

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 01:44:16

quote:
Originally posted by khtan

It will be easier if you could do the running total in your front end or reporting tool

----------------------------------
'KH'
everything that has a beginning has an end

Not difficult but inefficient

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 05:40:44

quote:
Originally posted by mmarovic

quote:
Originally posted by madhivanan

[quote]Originally posted by khtan

It will be easier if you could do the running total in your front end or reporting tool

----------------------------------
'KH'
everything that has a beginning has an end

Not difficult but inefficient

What do you mean? The most efficient solution is to handle running total on front end. The sql solution offered is one the least efficient. Even cursor would be much faster.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-13 : 06:00:20

Well, it would also have been nice, if MS would have implemented this OLAP SUM(...) OVER... PARTITION... thingy.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 07:14:41

Mirko, Actually I wanted to answer for this