Let me see if I can explain this in such a way that it makes sense to anyone other than myself...
I have written a simple query which pulls some member information from our members table (Member #, Full Name, First Name, Last Name, Join Date, Age, Active (whether or not their account is active Y/N) and Business Account (whether or not they have a business account Y/N depending on if MEMBER_TYPE_DESC contains the string 'Business') The code for that query is as follows;
SELECT MEMBER_ID AS 'Member #', MEMBER_FIRST_NAME + ' ' + MEMBER_LAST_NAME AS 'Full Name', MEMBER_FIRST_NAME AS 'First Name', MEMBER_LAST_NAME AS 'Last Name', JOIN_DATE AS 'Join Date',
(CONVERT(int,CONVERT(char(8),GetDate(),112))-CONVERT(char(8),BIRTH_DATE,112))/10000 AS 'Age', ACTIVE_FLAG AS 'Active',
'Business Account' =
WHEN MEMBER_TYPE_DESC = 'Business' THEN 'Y'
Super simple so far, right? But now I want to add an Auto Loan column which returns a Y/N response if the member has an Auto Loan with us. Unfortunately, to determine whether or not they have an Auto Loan I have to reference CURRENT_LOAN_ACCOUNT_ID column in the DIM_LOAN_ACCOUNT table.
The CURRENT_LOAN_ACCOUNT_ID is the Member # (eg. 12345), L (for loan) and the Loan Type (32 for auto loan), and if a member has more than one auto loan a .1, .2, .3 ect. will be added to the end of the CURRENT_LOAN_ACCOUNT_ID so that the loans can always be differentiated. So, for example, if member # 12345 has 2 auto loans there would be CURRENT_LOAN_ACCOUNT_ID's of 12345L32 and 12345L32.1 in the DIM_LOAN_ACCOUNT.
There is no MEMBER_ID (Member #) column in the DIM_LOAN_ACCOUNT table, so I have written another simple query that pulls the Member # from the DIM_MAMBER table and the CURRENT_LOAN_ACCOUNT_ID LIKE '%32%' from the DIM_LOAN_ACCOUNT table and joined them on the MEMBER_ID and LEFT(CURRENT_LOAN_ACCOUNT_ID, 5) as follows;
SELECT DIM_MEMBER.MEMBER_ID, DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID
FROM DIM_MEMBER INNER JOIN
DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID = LEFT(DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID, 5)
WHERE (DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID LIKE '%32%')
I would like to know how I can add the Auto Loan column to my original query that possibly uses the above query as a subquery in a CASE statement, such that if the Member # from the original query is listed in the subquery it returns a Y and if it is not then it returns a N.
I was thinking I could use the following code in the original SELECT statement, but it returned an error;
('Auto Loan' =
WHEN MEMBER_ID IN(SELECT DIM_MEMBER.MEMBER_ID, DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID
FROM DIM_MEMBER INNER JOIN
DIM_LOAN_ACCOUNT ON DIM_MEMBER.MEMBER_ID =
WHERE (DIM_LOAN_ACCOUNT.CURRENT_LOAN_ACCOUNT_ID LIKE '%32%'))