Correlated Query Problem
-
Hi,
I have a table with the following columns:
TransID int
TransDate datetime
SocietyID int
TransType varchar(10)
MobileNo varchar(20)
Amount int
and I want to return a "statement" from the table showing the TransDate, MobileNo, Amount and Balance so I ran the following query:
Select t1.transid,
t1.TransDate,
t1.TransType,
t1.MobileNo,
t1.amount,
sum(t2.Amount),
t1.SocietyID
from acc_transaction t1, acc_transaction t2
where t1.TransID >= t2.TransID
group by t1.TransID
having t1.SocietyID = 1
order BY t1.TransDate
but it's not calculating the balance correctly. What am I doing wrong? Also, rather than listing each TransID, I would like to group the results by date and just print one line per date rather than TransID. I'm going round in circles (like my query!!)
Please help,
Dec.
-
You should get an error message when you execute the sql above, I think...
Anyway, could you post some test data along with the expected result of the query you are looking for?
-
Hi,
Table Data (disappeared down the page):
TransID TransDate SocietyID TransType MobileNo Amount 1 20-Dec-05 1 Lodge 12345 100 2 20-Dec-05 1 Withdraw 12345 -10 3 21-Dec-05 1 Withdraw 12345 -5 4 22-Dec-05 1 Lodge 12345 50 5 23-Dec-05 1 Withdraw 12345 -5 Expected results:
TransDate SocietyID TransType Amount Balance 20-Dec-05 1 Lodge 100 100 20-Dec-05 1 Withdraw -10 90 21-Dec-05 1 Withdraw -5 85 22-Dec-05 1 Lodge 50 135 23-Dec-05 1 Withdraw -5 130 -
How about this?
declare @acc_transaction table(
TransID int,
TransDate datetime,
SocietyID int,
TransType varchar(10),
MobileNo varchar(20),
Amount int
)
insert @acc_transaction select 1, '20-Dec-05', 1, 'Lodge', 12345, 100
insert @acc_transaction select 2, '20-Dec-05', 1, 'Withdraw', 12345, -10
insert @acc_transaction select 3, '21-Dec-05', 1, 'Withdraw', 12345, -5
insert @acc_transaction select 4, '22-Dec-05', 1, 'Lodge', 12345, 50
insert @acc_transaction select 5, '23-Dec-05', 1, 'Withdraw', 12345, -5
select a1.TransDate, a1.SocietyID, a1.TransType, a1.Amount, sum(a2.Amount) as Balance
from @acc_transaction a1 inner join @acc_transaction a2 on a1.TransID >= a2.TransID
group by a1.TransDate, a1.SocietyID, a1.TransType, a1.Amount
-
Hi Jesper,
Yes, that's it, thanks. I think I was confusing myself by putting TransDates in the table out of sequence with the TransID
Thanks again,
Dec.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply