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 IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018
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