February 13, 2012 at 4:21 am
Good day,
Found this forum and hope it might help me in resolving my problem.
I have 4 tables namely;
Rim
Accounts
History EFT
History ATM
Heres what im trying to do, i want to generate a statistical report (query) for the bank, based on Rim, Accounts and history.
A clients opens a profile and gets assigned a Rim number, than the client has multiple accounts under that rim, those accounts have multiple transactions from different accounts which are hosted by one rim.
I want to do a select from both tables and combine the data as follow.
So far i have gone up to retrieving data from rims and accounts, theoretically i know what i want but how to accomplish it has become a nightmare.
You re help will be much appreciated.
Tables
Rim table
Rim_no
----------
12345
54321
Account table
Rim_no Accounts
------------------
12345 8000123
12345 8000123
54321 8000321
history table EFT
account_no tran_name amt
------------------------------------
8000123 pay mario x
8000123 deposit x
8000321 pay mario x
8000321 deposit x
history table ATM
account_no tran_name amt
------------------------------------
8000123 withdraw x
8000123 pos buy x
80012345 withdraw x
8000321 withdraw x
8000321 pos buy x
Rim Account_no Transactions total_accounts Total_charges Total_transactions
------------------------------------------------------------------------------------------
12345 2 199.25 5
------------------------------------------------------------------------------------------
8000123 157.25 4
pay mario
withdraw
deposit
pos buy
------------------------------------------------------------------------------------------
80012345 42.00 1
withdraw
------------------------------------------------------------------------------------------
54321 1 53.45 4
------------------------------------------------------------------------------------------
8000321 53.45 4
pay mario
withdraw
deposit
pos buy
------------------------------------------------------------------------------------------
February 13, 2012 at 5:45 am
Hi,
Do u want the result set in the reports (SSRS)?
If yes, you can use the below query to bring the result and by placing the columns with appropriate groupings in the report file (RDL).
Select a.Rim_no, b.Account_no,c.tran_name, count (distinct accounts), Sum (amt)
from [Rim table] a
inner join [Account table] b on a.rim_no = b. rim_no
inner join
(
select account_no, tran_name, amt from [history table EFT]
Union
select account_no, tran_name, amt from [history table ATM]
) C
on b.accounts = C.account_no
Group by a.Rim_no, b.Account_no,c.tran_name
The above solution is assumed to have the result in a report (RDL).
Thanks
Ramkumar.K
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
February 13, 2012 at 6:11 am
Hi Ramkumar.K,
Yes im trying to write a query than save it a stored proc, than i will use management to create a DRL report wich calls the store proc.
This is kinda challenging for me but i got something out it.
I have all the data i want, but i have it with multiple isolated select queries, now im trying to figure out how to combine this queries as one, my knowledge is primarly not so advanced but quite ok.
I will analyse your query and try to build a report.
AKA this part of my work, just took this report for the sake of learning and if i get it right, wow.
Here is what i have done so far:
select *
from atm_tran_log_CBL
where acct_no = '135200316004'
--14--rows
select *
from dp_history_CBL
where acct_no = '135200316004'
--271--rows
select distinct(a.acct_no), COUNT(*) as 'Transactions Per account', b.rim_no
from dp_history_CBL as a, dp_acct_CBL as b
where a.acct_no = b.acct_no
group by a.acct_no, b.rim_no
--29842
union
select distinct(c.acct_no), COUNT(*), d.rim_no
from atm_tran_log_CBL as c, dp_acct_CBL as d
where c.acct_no = d.acct_no
and c.acct_no = '135200316004'
group by c.acct_no,d.rim_no --1807
---31649
Thanks
February 13, 2012 at 6:29 am
Hi,
I guess this can help u
select R.Rim_no ,NULL Account_no,NULL as Transactions,count(accounts) TotalAccounts,SUM(amt)TotalCharges ,COUNT(tran_name)Total_transactions from Rim R Inner Join Accounts A on R.Rim_no=A.Rim_no Inner join Atm On A.accounts=Atm.acount_no group by R.rim_no
select Accounts,NULL as Transactions,count(accounts) TotalAccounts,SUM(amt)TotalCharges ,COUNT(tran_name)Total_transactions from Accounts A Inner join Atm On A.accounts=Atm.acount_no group by Accounts
select Rim_no,Accounts,Tran_name ,null TotalAccounts,SUM(amt) TotalCharges,COUNT(tran_name) from accounts A Inner join atm On A.accounts=atm.acount_no group by rim_no,Accounts,tran_name
February 13, 2012 at 7:30 am
Hi Ramkumar.K
Your query works fine and did 60% of what i want or needed.
There are transactions missing or not retrieved by the query.
I noted Rim_no 30734 which has only 1 account, in you query it returned 41 transactions but if i run against the ATM & EFT tables it returns 229 rows in EFT and nothing in ATM which is fine.
select *
from atm_tran_log_CBL
where acct_no = '115201229008'
--0
select *
from dp_history_CBL
where acct_no = '115201229008'
--229
If we can nail this down and get all the data than i can use this query and modify to fulfill my report.
Thanks in advance,
Yours Carnalito (Shynnie)
February 13, 2012 at 7:45 am
Hi guruprasad1987
Thank you, your help is very appriciated and thats really what i wanted.. i will just have to do the same the EFT and I will the other query with a UNION and walla i got my data set..
Thank you again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply