Selecting data from multiple tables

  • 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_noAccounts

    ------------------

    123458000123

    123458000123

    543218000321

    history table EFT

    account_notran_nameamt

    ------------------------------------

    8000123pay mariox

    8000123depositx

    8000321pay mariox

    8000321depositx

    history table ATM

    account_notran_nameamt

    ------------------------------------

    8000123withdrawx

    8000123pos buyx

    80012345withdrawx

    8000321withdrawx

    8000321pos buyx

    RimAccount_noTransactionstotal_accountsTotal_chargesTotal_transactions

    ------------------------------------------------------------------------------------------

    123452199.255

    ------------------------------------------------------------------------------------------

    8000123157.254

    pay mario

    withdraw

    deposit

    pos buy

    ------------------------------------------------------------------------------------------

    8001234542.001

    withdraw

    ------------------------------------------------------------------------------------------

    54321153.454

    ------------------------------------------------------------------------------------------

    800032153.454

    pay mario

    withdraw

    deposit

    pos buy

    ------------------------------------------------------------------------------------------

  • 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....
    ######################

  • 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

  • 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

  • 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)

  • 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