August 18, 2015 at 4:23 pm
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,
LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
Loan ID First Name Lastname Address address 2 City State Zip
1236048 Joey Yesen xxxx abc GROVE RD NULL CLEVELAND TX 77327
1236048 Dickey Yesen xxxx abc GROVE RD NULL CLEVELAND TX 77327
1235983 Randy Seany xxxx abc Haleyville St NULL Aurora CO 80018
1235983 Barry Seany xxxx abc Haleyville St NULL Aurora CO 80018
The query I am using
select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'
August 18, 2015 at 5:13 pm
hmm normally addresses are attached to the borrower not the loan. but whatever.
Without some table structure knowledge (ie: do borrowers have ID's?)
Anyway something like this below will work, I copied your data into a basic structure so I could test it. Not optimal if you have vast rows, probably not going to be good if a loan has 3 people attached to it, but that's what ya get when you flatten data like this.
You'll have to add back in your status table, I didn't bother recreating that. And the deceased = 0 I moved into the CTE. You could have used the 'Borrower_cte' as a couple of subquery instead of a CTE. not sure which is faster, but you can test on the full table. That second alternative is below
with Borrower_cte (loanid, LastName, FirstMiddleName, DRANK)
AS
(Select loanid, LastName, FirstMiddleName, dense_rank() Over( partition by loanid order by Lastname, FirstMiddleName)
from dbo.Borrower where Deceased = 0
)
select
L.Loanid
,B1.FirstMiddleName
,B1.LastName
,B2.FirstMiddleName
,B2.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
--LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT outer JOIN Borrower_CTE B1 on L.LoanID = B1.loanid and B1.DRANK = 1
LEFT outer JOIN Borrower_CTE B2 on L.LoanID = B2.loanid and B2.DRANK = 2
--where S.PrimStat = '1'
---- second alternative:
select
L.Loanid
,B1.FirstMiddleName
,B1.LastName
,B2.FirstMiddleName
,B2.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
--LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT outer JOIN (Select loanid, LastName, FirstMiddleName, dense_rank() Over( partition by loanid order by Lastname, FirstMiddleName) as DRANK from dbo.Borrower where Deceased = 0 ) B1 on L.LoanID = B1.loanid and B1.DRANK = 1
LEFT outer JOIN (Select loanid, LastName, FirstMiddleName, dense_rank() Over( partition by loanid order by Lastname, FirstMiddleName) as DRANK from dbo.Borrower where Deceased = 0 ) B2 on L.LoanID = B2.loanid and B2.DRANK = 2
August 18, 2015 at 6:43 pm
Thank you so much, I will try tomorrow and see if its working, seems like you already tested it and it is working. what output are you getting?
August 18, 2015 at 6:50 pm
Damn already done the clean up, but basically just the two lines as you wished.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply