SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to calculate balance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ceema
Yak Posting Veteran

80 Posts

Posted - 02/07/2006 :  03:55:23  Show Profile  Reply with Quote
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

India
394 Posts

Posted - 02/07/2006 :  04:14:17  Show Profile  Reply with Quote
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'
Go to Top of Page

ceema
Yak Posting Veteran

80 Posts

Posted - 02/07/2006 :  04:19:56  Show Profile  Reply with Quote
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
Go to Top of Page

shallu1_gupta
Constraint Violating Yak Guru

India
394 Posts

Posted - 02/07/2006 :  04:32:26  Show Profile  Reply with Quote
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
Go to Top of Page

ceema
Yak Posting Veteran

80 Posts

Posted - 02/07/2006 :  04:45:41  Show Profile  Reply with Quote
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
Go to Top of Page

shallu1_gupta
Constraint Violating Yak Guru

India
394 Posts

Posted - 02/07/2006 :  05:56:07  Show Profile  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 02/07/2006 :  05:57:38  Show Profile  Reply with Quote
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'


Go to Top of Page

ceema
Yak Posting Veteran

80 Posts

Posted - 02/07/2006 :  06:33:02  Show Profile  Reply with Quote
Thank you Khan,

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

Ceema
Go to Top of Page

ceema
Yak Posting Veteran

80 Posts

Posted - 02/07/2006 :  06:36:57  Show Profile  Reply with Quote
Shallu,

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

Thanks
Ceema
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/07/2006 :  07:19:23  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by ceema

Shallu,

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

Thanks
Ceema



Of course.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 02/07/2006 :  07:20:46  Show Profile  Reply with Quote
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'


Go to Top of Page

ceema
Yak Posting Veteran

80 Posts

Posted - 02/07/2006 :  07:23:21  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 02/07/2006 :  07:46:39  Show Profile  Reply with Quote
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


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


Go to Top of Page

ceema
Yak Posting Veteran

80 Posts

Posted - 02/07/2006 :  08:14:46  Show Profile  Reply with Quote
Dear Khan,

Thank you so much

Ceema
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000