Solved

Select statement that will only give me the latest (max) date where multiple records exist for each ID

Posted on 2012-12-28
6
314 Views
Last Modified: 2012-12-28
I have a table that shows multiple records per ID, it tracks the status of a loan in process and many times people will record the same status more than one time.  I'm looking for a select statement that will give me just the latest date that the loan was moved to it's current status. So in other words the max date for the max status but I can't use an aggregate function like (MAX).

for example my current select statement;
SELECT DISTINCT
                      TOP (100) PERCENT dbo.setups_loanstatus.statusdescription AS [Latest Status], dbo.loan_main.loanid, dbo.setups_loanstatus.statusorder, dbo.loan_status.statusid,
                      dbo.loan_status.datefirstentered, dbo.loan_main.lenderdatabaseid, dbo.loan_main.loanrecordid
FROM         dbo.loan_main INNER JOIN
                      dbo.setups_loanstatus ON dbo.loan_main.statusid = dbo.setups_loanstatus.statusid LEFT OUTER JOIN
                      dbo.loan_status ON dbo.setups_loanstatus.statusid = dbo.loan_status.statusid AND dbo.loan_main.lenderdatabaseid = dbo.loan_status.lenderdatabaseid AND
                      dbo.loan_main.loanrecordid = dbo.loan_status.loanrecordid
ORDER BY dbo.loan_main.loanid DESC, dbo.setups_loanstatus.statusorder DESC

gives me the correct date for the current status but if the loan has been in that status more than once I get two records for that Loan ID.
0
Comment
Question by:ISBTECH
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 38727548
Try using ROW_NUMBER()

;WITH CTE AS (
SELECT DISTINCT
                      TOP (100) PERCENT dbo.setups_loanstatus.statusdescription AS [Latest Status], dbo.loan_main.loanid, dbo.setups_loanstatus.statusorder, dbo.loan_status.statusid,
                      dbo.loan_status.datefirstentered, dbo.loan_main.lenderdatabaseid, dbo.loan_main.loanrecordid,
ROW_NUMBER() OVER (PARTITION BY LoanID, StatusID ORDER BY DateFirstEntered DESC) RN
FROM         dbo.loan_main INNER JOIN
                      dbo.setups_loanstatus ON dbo.loan_main.statusid = dbo.setups_loanstatus.statusid LEFT OUTER JOIN
                      dbo.loan_status ON dbo.setups_loanstatus.statusid = dbo.loan_status.statusid AND dbo.loan_main.lenderdatabaseid = dbo.loan_status.lenderdatabaseid AND
                      dbo.loan_main.loanrecordid = dbo.loan_status.loanrecordid)

SELECT
   *
FROM
   CTE
WHERE
   RN = 1
0
 

Author Comment

by:ISBTECH
ID: 38728203
Ok, that works but (I didn't mention this before, didn't think it matters) I'm using the select statement for a view and when I use that text in the view it crashes SQL management studio, any way it can be done to allow use in a view?
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 38728209
try it without the CTE like this.

SELECT
    *
FROM
    (
     SELECT DISTINCT TOP (100) PERCENT
        dbo.setups_loanstatus.statusdescription AS [Latest Status]
       ,dbo.loan_main.loanid
       ,dbo.setups_loanstatus.statusorder
       ,dbo.loan_status.statusid
       ,dbo.loan_status.datefirstentered
       ,dbo.loan_main.lenderdatabaseid
       ,dbo.loan_main.loanrecordid
       ,ROW_NUMBER() OVER (PARTITION BY LoanID, StatusID ORDER BY DateFirstEntered DESC) RN
     FROM
        dbo.loan_main
        INNER JOIN dbo.setups_loanstatus
            ON dbo.loan_main.statusid = dbo.setups_loanstatus.statusid
        LEFT OUTER JOIN dbo.loan_status
            ON dbo.setups_loanstatus.statusid = dbo.loan_status.statusid
               AND dbo.loan_main.lenderdatabaseid = dbo.loan_status.lenderdatabaseid
               AND dbo.loan_main.loanrecordid = dbo.loan_status.loanrecordid
    ) a
WHERE
    RN = 1

Open in new window

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: 38728238
Strange, still crashes management studio as a view but works fine as a query.
0
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 38728287
Try creating it without using the GUI.  

CREATE  VIEW [dbo].[YourViewName]
AS
    SELECT
        *
    FROM
        (
         SELECT DISTINCT TOP (100) PERCENT
            dbo.setups_loanstatus.statusdescription AS [Latest Status]
           ,dbo.loan_main.loanid
           ,dbo.setups_loanstatus.statusorder
           ,dbo.loan_status.statusid
           ,dbo.loan_status.datefirstentered
           ,dbo.loan_main.lenderdatabaseid
           ,dbo.loan_main.loanrecordid
           ,ROW_NUMBER() OVER (PARTITION BY LoanID, StatusID ORDER BY DateFirstEntered DESC) RN
         FROM
            dbo.loan_main
            INNER JOIN dbo.setups_loanstatus
                ON dbo.loan_main.statusid = dbo.setups_loanstatus.statusid
            LEFT OUTER JOIN dbo.loan_status
                ON dbo.setups_loanstatus.statusid = dbo.loan_status.statusid
                   AND dbo.loan_main.lenderdatabaseid = dbo.loan_status.lenderdatabaseid
                   AND dbo.loan_main.loanrecordid = dbo.loan_status.loanrecordid
        ) a
    WHERE
        RN = 1

GO

Open in new window

0
 

Author Closing Comment

by:ISBTECH
ID: 38728291
Yup that works, can't view it in the GUI but the data is there, Thanks!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 35
Inserting oldest record into new table. 5 24
always on switch back after failover 2 35
delete the first occurence of a duplicate row in sql 4 22
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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
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.

773 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