Solved

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

Posted on 2013-12-05
6
410 Views
Last Modified: 2013-12-05
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
0
Comment
Question by:ISBTECH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39698758
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>
0
 

Author Comment

by:ISBTECH
ID: 39698954
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'.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39698968
The ellipse and the last line are not literal.  You will have to do a little bit of the work yourself.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ISBTECH
ID: 39699010
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?
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39699016
You've got an extra comma at the end of the SELECT clause
0
 

Author Closing Comment

by:ISBTECH
ID: 39699095
Ah! that was it, Thanks!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question