Solved

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

Posted on 2013-12-05
6
408 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tools to scan a SQL server's problem 14 28
TSQL convert date to string 4 37
Problems "swapping" SQL Server DBs 2 23
Isolation level setting TSQL View 10 29
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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