How to 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)

How to calculate balance

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-07 : 03:55:23

Hello,

I have one table 'Arrear' with columns SchoolID,RegNo,Period,FeeDescription,Debit,Credit. My problem is I have to calculate the balance amount(debit-credit)
when I will pass schoolid,regno & period to the table.

Like I have

SchoolID RegNo Period FeeDesc Debit Credit
----------------------------------------------------------------------
Ab0 1111 12/12/2005 Test1 12.00 00.00
Ab0 1111 13/12/2005 test2 14.00 00.00
Ab0 2222 13/12/2005 test1 12.00 00.00
Ab0 1111 14/12/2005 test3 00.00 10.00
Ab0 1111 15/12/2005 test3 00.00 16.00
Ab0 1111 13/12/2005 test5 10.00 00.00
Ab0 1111 13/12/2005 test6 00.00 20.00

So, when I pass ab0 for schoolid,11/12/2005 as starting period & 14/12/2005 as ending period

I should get the result

period balance
-----------------------
12/12/2005 12.00
13/12/2005 16.00
14/12/2005 6.00

How could I achieve it?

Thanks
Ceema

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-07 : 04:14:17

try this
select period, balance = (debit-credit)
from arrear
where schoolid = 'ab0'
and
select dateadd(day, 0, datediff(day, 0, period)) between '12/11/2005' and '12/14/2005'
and regnno= '1111'

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-07 : 04:19:56

Hello Shallu & Harshal,

Both the queries ar not working. I have tried

SELECT A0.Period,
SUM(A1.debit - A1.credit) AS Balance
FROM Statements A0
JOIN Statements A1 ON A1.SchoolId = A0.SchoolId
AND A1.RegNo=A0.RegNo
AND A1.Period <= A0.Period
WHERE A0.SchoolId = 'ab0' and A0.RegNo='1111'
AND A0.Period BETWEEN CONVERT(datetime,'11/12/20054') AND CONVERT(datetime,'14/12/20054')
AND A1.Period BETWEEN CONVERT(datetime,'11/12/20054') AND CONVERT(datetime,'14/12/20054')
group by A0.Period

Then, some multiple values are coming for day 13th(others are correct) sicne it's having multiple entries.

Please help .

Thanks
Ceema

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-07 : 04:32:26

why are you doing a self join if you need grouping by period then add a group by clause.
select period, balance = sum(debit-credit)
from arrear
where schoolid = 'ab0'
and
dateadd(day, 0, datediff(day, 0, period)) between '12/11/2005' and '12/14/2005'
and regno= '1111'
group by period

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-07 : 04:45:41

Hello Shallu,

The query you have given is giving only one result, 12/12/2005 12.00. But I want it like the following.

period balance
-----------------------
12/12/2005 12.00
13/12/2005 16.00
14/12/2005 6.00

Thanks
Ceema

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-07 : 05:56:07

Hi,
Not sure how come it is returning just one row?
Ok tell me
what is the datatype of period field?
Do the period also contain time value?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 05:57:38

Ceema,

Looking at the expected result, i would said the balance is a cummulative balance. If it is so, you should have state this clearly.

declare
	@SchoolID	varchar(10),
	@RegNo		int,
	@start_period	datetime,
	@end_period	datetime

select	@SchoolID	= 'Ab0',
	@RegNo		= 1111,
	@start_period	= '2005-12-11',
	@end_period	= '2005-12-14'

select 	a.SchoolID, a.RegNo, Period, (select sum(Debit - Credit) from Arrear x 
					where 	x.SchoolID	= a.SchoolID
					and	x.RegNo		= a.RegNo
					and	x.Period 	<= a.Period) as [Cummulative Balance]
from 	Arrear a
where	a.SchoolID	= @SchoolID
and	a.RegNo		= @RegNo
and	a.Period	>= @start_period
and	a.Period	<= @end_period
group by a.SchoolID, a.RegNo, a.Period

----------------------------------
'KH'

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-07 : 06:33:02

Thank you Khan,

You are right, this time, I have got the exact result. Thank you so much

Ceema

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-07 : 06:36:57

Shallu,

Period doesn't contain time value, it's varchar.

Thanks
Ceema

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-07 : 07:19:23

quote:
Originally posted by ceema

Shallu,

Period doesn't contain time value, it's varchar.

Thanks
Ceema

Of course.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 07:20:46

quote:
Originally posted by ceema

Shallu,

Period doesn't contain time value, it's varchar.

Thanks
Ceema

Should be using datatime data type for storing date value.

----------------------------------
'KH'

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-07 : 07:23:21

One more thing, If i have to display the negative values as Cr. in the same query result, what should I do, example, I am getting -10 in one row for 15/12/2005 as the result, it should come like '10 Cr', please give me a solution

Thanks
Ceema

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 07:46:39

[code]declare
@SchoolID varchar(10),
@RegNo int,
@start_period datetime,
@end_period datetime
select @SchoolID = 'Ab0',
@RegNo = 1111,
@start_period = '2005-12-11',
@end_period = '2005-12-15'

select SchoolID, RegNo, Period, convert(varchar, abs(Balance)) + (case when Balance < 0 then ' Cr' else '' end)
from
(
select a.SchoolID, a.RegNo, Period, (select sum(Debit - Credit) from Arrear x
where x.SchoolID = a.SchoolID
and x.RegNo = a.RegNo
and x.Period <= a.Period) as Balance
from Arrear a
where a.SchoolID = @SchoolID
and a.RegNo = @RegNo
and a.Period >= @start_period
and a.Period <= @end_period
group by a.SchoolID, a.RegNo, a.Period
) as b[/code]

----------------------------------
'KH'

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-02-07 : 08:14:46

Dear Khan,

Thank you so much

Ceema

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources