Solved

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

Posted on 2013-12-05
6
411 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

617 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