asked on

SQL Query to return one record when accessing a table with multiple records per loan

Good Morning Experts,

I am trying to write a query to return just one record per loan from a database table that stores information for each loan in multiple records. In this case the table used are the main loan table (Loan_Main) and the table that stores information on the Underwriting conditions applied to each loan (Loan_Conditions).

The unique identifier for each loan is the LoanRecordID, and each spereate condition has a ConditionID. Here is the problem, I'm trying to get a result in one record per loan, so only hitting the same loanrecordid once each loan, however I need to show the field "DateAdded" for each condition on the loan, for example if the loan had 6 condition it would be stored in the loan_conditions table like this

LoanID1 ConditonID1 DateAdded
LoanID1 ConditonID2 DateAdded
LoanID1 ConditonID3 DateAdded
LoanID1 ConditonID4 DateAdded
LoanID1 ConditonID5 DateAdded
LoanID1 ConditonID6 DateAdded

I need to return this:
LoanID1 ConditionID1_DateAdded ConditionID2_DateAdded ConditionID3_DateAdded

etc.

I've tried this:
SELECT lm.lenderdatabaseid as LenderDatabaseID
            , lm.loanrecordid as LoanRecordID
            , case when lc.condid = 1 then lc.dateadded end as Cond1_DateAdded
            , case when lc.condid = 2 then lc.dateadded end as Cond2_DateAdded

FROM      Loan_Main as lm Left Outer JOIN
            Loan_Conditions as lc ON lm.loanrecordid = lc.loanrecordid AND lm.lenderdatabaseid = lc.lenderdatabaseid

WHERE      lc.categoryid = 29 AND lm.loanrecordid = 6678

but that returns:

LenderDatabaseID      LoanRecordID      Cond1_DateAdded        Cond2_DateAdded
1        6678        2013-12-05 00:00:00.000      NULL
1        6678        NULL        2013-12-05 00:00:00.000

Brian Crowe

WITH cteCondition (LoanRecordID, LoanConditionID, DateAdded, RowNumber)
AS
(
SELECT LoandRecordID< LoanConditionID, DateAdded,
ROW_NUMBER() OVER(PARTITION BY LoanRecrodID ORDER BY DateAdded) AS RowNumber
FROM Loan_Conditions
)
SELECT L.lenderdatabaseid AS LenderDatabaseID,
L.loanrecordid AS LoanRecordID,
C1.DateAdded AS Cond1_DateAdded,
C2.DateAdded AS Cond2_DateAdded,
...
FROM Loan_Main AS L
LEFT OUTER JOIN cteCondition AS C1
ON L.LoanRecordID = C1.LoanRecordID
AND C1.RowNumber = 1
LEFT OUTER JOIN cteCondition AS C2
ON L.LoanRecordID = C2.LoanRecordID
AND C2.RowNumber = 2
<repeat for as many conditions as you want>

ISBTECH

ASKER

When I run that I get:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '<'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FROM'.

Brian Crowe

The ellipse and the last line are not literal. You will have to do a little bit of the work yourself.

ISBTECH

ASKER

I have no problem doing work myself however your way over my head on SQL here so I don't know what characters might be valid that I don't know about.

I cleaned it up and changed the column names to match my database and I get this:
WITH cteCondition (LoanRecordID, CondID, DateAdded, RowNumber)
AS
(
SELECT LoandRecordID, CondID, DateAdded,
ROW_NUMBER() OVER(PARTITION BY LoanRecordID ORDER BY DateAdded) AS RowNumber
FROM Loan_Conditions
)
SELECT L.lenderdatabaseid AS LenderDatabaseID,
L.loanrecordid AS LoanRecordID,
C1.DateAdded AS Cond1_DateAdded,
C2.DateAdded AS Cond2_DateAdded,

FROM Loan_Main AS L
LEFT OUTER JOIN cteCondition AS C1
ON L.LoanRecordID = C1.LoanRecordID
AND C1.RowNumber = 1
LEFT OUTER JOIN cteCondition AS C2
ON L.LoanRecordID = C2.LoanRecordID
AND C2.RowNumber = 2

but that still returns:
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FROM'.

I cannot see anything incorrect about the From segment, granted I've never used cteCondition so I don't know if it's missing something?

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ISBTECH

ASKER

Ah! that was it, Thanks!