SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 32 of 32
  1. #26
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks correct
    Just one question; when two customers or more are related to a deposit/loan, is it correct to display the total deposit/loan for each customer, or should the total be divided by 2, 3, etc?

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that is a most excellent question

    one i did not anticipate

    and indeed, there is one account with this situation, and also one loan

    so my answer is close, but no cigar

    i.e. wrong

  3. #28
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i've fixed my solution

    at the very beginning, define views that give the number of customers for each account and loan:

    Query 2aCount count customers by account by branch:
    Code:
    SELECT account.branch_name, account.account_number
         , account.balance
         , Count(depositor.customer_name) AS customers
      FROM account 
    INNER JOIN depositor 
        ON account.account_number = depositor.account_number
    GROUP BY account.branch_name, account.account_number
         , account.balance;
    Query 2bCount count customers by loan by branch:
    Code:
    SELECT loan.branch_name, loan.loan_number
         , loan.amount
         , Count(borrower.customer_name) AS customers
      FROM loan 
    INNER JOIN borrower 
        ON loan.loan_number = borrower.loan_number
    GROUP BY loan.branch_name, loan.loan_number
         , loan.amount;
    now use these views instead of the account and loan tables

    Query 2a summarize customer accounts at each branch:
    Code:
    SELECT customer.customer_name, account.branch_name
         , Sum(account.balance / account.customers) AS total_deposit
    FROM ( customer 
    INNER JOIN depositor 
          ON customer.customer_name = depositor.customer_name )
    INNER JOIN Query2aCount as account
          ON depositor.account_number = account.account_number
    GROUP BY customer.customer_name, account.branch_name;
    Query 2b summarize customer loans at each branch:
    Code:
    SELECT customer.customer_name, loan.branch_name
         , Sum(loan.amount / loan.customers) AS total_loan
    FROM ( customer 
    INNER JOIN borrower 
          ON customer.customer_name = borrower.customer_name )
    INNER JOIN Query2bCount as loan
          ON borrower.loan_number = loan.loan_number
    GROUP BY customer.customer_name, loan.branch_name;
    the final query needs no alteration

    Query 2c customers, with accounts and loans by branch:
    Code:
    select Query2a.customer_name, Query2a.branch_name
         , Query2a.total_deposit  
         , IIF( isnull(Query2b.total_loan), 0, Query2b.total_loan )
             as total_loan
      from Query2a 
    LEFT JOIN Query2b 
        on Query2a.branch_name = Query2b.branch_name
       and Query2a.customer_name = Query2b.customer_name
    
    UNION
    
    select Query2b.customer_name, Query2b.branch_name
         , IIF( isnull(Query2a.total_deposit), 0, Query2a.total_deposit )
             as total_deposit
         , Query2b.total_loan
      from Query2a 
    RIGHT JOIN Query2b 
        on Query2a.customer_name = Query2b.customer_name
       and Query2a.branch_name = Query2b.branch_name
    
    UNION
    
    select customer.customer_name, null, null, null
      from customer 
     where NOT EXISTS
           ( select 1 from borrower 
              where customer_name = customer.customer_name )
       and NOT EXISTS
           ( select 1 from depositor 
              where customer_name = customer.customer_name )
    
    ORDER BY 1, 2;
    Results:
    Code:
    customer   branch       total    total
    name       name        deposit    loan
    Adams      Perryridge      0      1300
    Brooks              
    Curry      Mianus          0       500
    Glenn              
    Green              
    Hayes      Downtown      250         0
    Hayes      Perryridge    400      1500
    Jackson    Downtown        0      1500
    Johnson    Downtown      250         0
    Johnson    Perryridge    900         0
    Jones      Brighton      750         0
    Jones      Downtown        0       500
    Lindsay    Redwood       700         0
    Majeris    Central       850         0
    McBride    North Town      0      7500
    Smith      Central         0       570
    Smith      Mianus        700         0
    Smith      North Town    625         0
    Smith      Redwood         0      2000
    Smith      Round Hill      0       900
    Turner     Round Hill    350         0
    Williams   Downtown        0       500

  4. #29
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    that is a most excellent question...
    Yep, and the answer isn't necessarily that your first query was wrong
    E.g. when I log in to my bank account to check how much I owe my bank (you don't want to know how much... ), I can read a number X. However, we are two person's "sharing" that loan, but if my wife should die tomorrow (which I hope she will not, not only because of this loan thing ), I guess I will still owe the bank the amount X, not X/2

  5. #30
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy's question then?


  6. #31
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everyone agree that rudy's answer is correct?
    (OK, I have some problems with the IIF() function, is that one standard sql? But I guess you could change it to COALESCE() or something?...)

  7. #32
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The next question is followed up here ->
    http://www.sitepointforums.com/showt...threadid=97083


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •