Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
412 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

704 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