| Author |
 Topic  |
|
|
ceema
Yak Posting Veteran
80 Posts |
 Posted - 02/07/2006 : 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
India
394 Posts |
Posted - 02/07/2006 : 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 - 02/07/2006 : 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
India
394 Posts |
Posted - 02/07/2006 : 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 - 02/07/2006 : 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
India
394 Posts |
Posted - 02/07/2006 : 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)
Singapore
16769 Posts |
Posted - 02/07/2006 : 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 - 02/07/2006 : 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 - 02/07/2006 : 06:36:57
|
Shallu,
Period doesn't contain time value, it's varchar.
Thanks
Ceema |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 02/07/2006 : 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)
Singapore
16769 Posts |
Posted - 02/07/2006 : 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 - 02/07/2006 : 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)
Singapore
16769 Posts |
Posted - 02/07/2006 : 07:46:39
|
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'
|
|
|
|
ceema
Yak Posting Veteran
80 Posts |
Posted - 02/07/2006 : 08:14:46
|
Dear Khan,
Thank you so much
Ceema |
|
|
| |
Topic |
|
How to calculate balance
