September 16, 2015 at 2:06 pm
Want to see if we can solve this problem by self join or it can be only done by CTE? If only CTE can somebody help me with the CTE.
Currently I am using the following code and the getting the following results, please see the attached excel file for the actual and expected result set.
app_decline_code is connected with app_decline_list on cust_id. the problem is there are 69 decline id's, which would be a nightmare if I do self joins for the expected columns (Decline reason 2 , 3 and 4)
Any help would be highly appreciated
SELECT
AP.first_name AS [First Name]
,AP.last_name AS [Last Name]
,CA.house_num + ' ' + CA.street_name + ' ' + CA.apt_num AS [Mailing Address]
,CA.city AS [City]
,CA.state AS [State]
,CA.zip_code AS [Zip Code]
,P.product_name AS [Product Name]
,DL.decline_desc AS [Decline Reason]
,CONVERT(VARCHAR(10), CAP.decision_date, 101) AS [Decision Date]
,CBI.name AS [Credit Bureau]
FROM applicant AS AP
JOIN credit_application AS CAP
ON AP.application_id = CAP.application_id
JOIN cust_address AS CA
ON AP.cust_id = CA.cust_id
JOIN product AS P
On P.product_id = CAP.product_id
JOIN dealer_vendor_product AS DP
ON P.product_id = DP.product_id
JOIN app_decline_code AS DC
ON AP.application_id = DC.application_id
JOIN app_decline_list AS DL
ON DC.decline_id = DL.decline_id
JOIN cb_rpt_ownership AS CR
ON AP.cust_id = CR.cust_id
JOIN cb_report AS CBR
ON CR.cb_rpt_id = CBR.cb_rpt_id
JOIN credit_bureau AS CBI
ON CBR.cb_id = CBI.cb_id
Where AP.application_id = '1075'
September 18, 2015 at 9:59 am
Hi Everyone,
I was able to write the CTE for the decline reasons I mentioned above, if you look below, another problem I ran into was for credit bureau. I want all the three credit bureau in a single row, where as of now if there are more than one credit bureau for 1 application it list under 2 different rows. Please see the attached excel for current results and expected result set.
Would using another CTE instead of self joins for the credit bureau an option. Or is there something wrong my joins??
the code I am using is below.
WITH app_decline_Code_CTE
AS
(
SELECT
application_id
,DC.decline_id
,DL.decline_desc
,dense_rank() OVER (PARTITION BY application_id ORDER BY DC.decline_id) AS [Dense Rank]
FROM dbo.app_decline_code AS DC
INNER JOIN app_decline_list AS DL
ON DC.decline_id = DL.decline_id
)
SELECT
DISTINCT AP.application_id
,CAST('6' as varchar(20)) AS [Document Code]
,CAST('M' as varchar(20)) AS [Company Name]
,AP.first_name AS [First Name]
,AP.last_name AS [Last Name]
,CA.house_num + ' ' + CA.street_name + ' ' + CA.apt_num AS [Mailing Address]
,CA.city AS [City]
,CA.state AS [State]
,CA.zip_code AS [Zip Code]
,SO.name AS [Dealer Name]
,P.product_name AS [Product Name]
,A1.decline_desc AS [Decline Reason 1]
,A2.decline_desc AS [Decline Reason 2]
,A3.decline_desc AS [Decline Reason 3]
,A4.decline_desc AS [Decline Reason 4]
,CONVERT(VARCHAR(10), CAP.decision_date, 101) AS [Decision Date]
,Equifax.name AS [Credit Bureau 1]
,Experian.name AS [Credit Bureau 2]
,TransUnion.name AS [Credit Bureau 3]
FROM applicant AS AP
JOIN credit_application AS CAP
ON AP.application_id = CAP.application_id
JOIN cust_address AS CA
ON AP.cust_id = CA.cust_id
LEFT JOIN product AS P
On P.product_id = CAP.product_id
--JOIN dealer_vendor_product AS DP
--ON P.product_id = DP.product_id
LEFT JOIN app_decline_code AS DC
ON AP.application_id = DC.application_id
JOIN app_decline_list AS DL
ON DC.decline_id = DL.decline_id
LEFT JOIN cb_rpt_ownership AS CR
ON AP.cust_id = CR.cust_id
LEFT JOIN cb_report AS CBR
ON CR.cb_rpt_id = CBR.cb_rpt_id
LEFT JOIN credit_bureau AS Equifax
ON CBR.cb_id = Equifax.cb_id AND Equifax.name = 'Equifax'
LEFT OUTER JOIN credit_bureau AS Experian
ON CBR.cb_id = Experian.cb_id AND Experian.name = 'Experian'
LEFT OUTER JOIN credit_bureau AS TransUnion
ON CBR.cb_id = TransUnion.cb_id AND TransUnion.name = 'TransUnion'
JOIN source_office AS SO
ON CAP.src_office_id = SO.src_office_id
LEFT outer JOIN app_decline_Code_CTE AS A1
ON AP.application_id = A1.application_id and A1.[Dense Rank] = 1
LEFT outer JOIN app_decline_Code_CTE AS A2
ON AP.application_id = A2.application_id and A2.[Dense Rank] = 2
LEFT outer JOIN app_decline_Code_CTE AS A3
ON AP.application_id = A3.application_id and A3.[Dense Rank] = 3
LEFT outer JOIN app_decline_Code_CTE AS A4
ON AP.application_id = A4.application_id and A4.[Dense Rank] = 4
WHERE AP.application_id = '1313'
September 18, 2015 at 10:18 am
I'd probably be using something like the code in your first post, with PIVOT or the cross-tab method.
Jeff has a couple of articles on it here: http://www.sqlservercentral.com/articles/T-SQL/63681/
With that many decline reasons your select list's not going to be pretty but at least it's only 1 join.
Remember with a CTE it's expanded for every call made to it.
Some DDL & test data would really help here, as per http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 18, 2015 at 11:12 am
any other possible solutions?
September 18, 2015 at 11:53 am
Would a comma delimited list of decline reasons be an acceptable solution? If it isn't then Gazareth's suggestion(s) are the best ones. If the decline list is basically static then I'd go with PIVOT, but if there are reasons added regularly I'd go with the dynamic cross tab as described by Jeff Moden in the article Gazareth linked to.
Another question would be, what are you using for display? Pivoting using SSRS or other reporting tool is usually simpler than doing it in SQL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply