Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-05
6
Medium Priority
?
413 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

824 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