April 26, 2017 at 9:08 am
I have 3 tables:
The first is a joining table for the others. A given user may have multiple loan and savers accounts. The account numbers are unique, but there is no way to determine whether an account is a loan or savers account without joining. This is a legacy system so I cannot change it.
I am trying to determine which accounts have both loan and savers accounts, and are not closed. I can determine which have multiple open loan accounts using the following:
Select UserName,
Count(AccountNo) As NumAccounts
From UserAccount usa
Inner Join LoanAccount lac On lac.AccNo= usa.AccountNo
Where lac.CloseDate Is Null
Group By UserName
Having Count(AccountNo) > 1
Order By NumAccounts Desc
and a similar query for savers, but am struggling to write one that combines this so that I get a list of useful users holding both loan and savers accounts. Any suggestions would be welcome.
April 26, 2017 at 9:26 am
Something like this may work for you. If it doesn't, you're going to need to post table DDL (CREATE TABLE statements), sample data (INSERT statements) and expected results.
Select UserName,
Count(lac.AccountNo) As LoanAccounts,
COUNT(sac.AccountNo) AS SaveAccounts
From UserAccount usa
Inner Join LoanAcc lac On lac.AccountNo = usa.AccountNo
INNER JOIN SaveAccount sac ON usa.AccountNo = sac.AccountNo
Where lac.CloseDate Is Null
AND sac.CloseDate IS NULL
Group By UserName
John
April 26, 2017 at 11:50 am
Thanks John. Sorry for the confusion, I changed the object names for security reasons. I have amended the SQL in the post now. I tried your suggestion, and as I suspected, it returned nothing. I changed the Inner Joins to Left Joins, as an account number will only ever appear in one of the 2 joined tables. That brought back results. However, ideally, I wanted to be able to determine users who had open accounts of both types. Currently, I see a number relating to the number of accounts for a user, but can not, without further querying, see that the user has both types of account.
I think I have solved this now. I amended the SQL as follows:
Select usa.UserName,
Count(lac.AccNo) + Count(sac.AccNo) As NumTotal,
Count(lac.AccNo) As NumLoanAccounts,
Count(sac.AccNo) As NumSaveAccounts
From UserAccount usa
Left Join LoanAccount lac On lac.AccNo = usa.AccountNo
Left Join SaveAccount sac On sac.AccNo = usa.AccountNo
Where lac.CloseDate is Null
And sac.CloseDate Is Null
Group By usa.UserName
Having Count(lac.AccNo) > 0
And Count(sac.AccNo) > 0
Order By NumTotal Desc
and it seems to be working fine now. Thanks for your assistance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply