Avatar of ISBTECH
ISBTECH
Flag for United States of America asked on

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

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.
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
ISBTECH

8/22/2022 - Mon
tim_cs

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
ISBTECH

ASKER
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?
tim_cs

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ISBTECH

ASKER
Strange, still crashes management studio as a view but works fine as a query.
ASKER CERTIFIED SOLUTION
tim_cs

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ISBTECH

ASKER
Yup that works, can't view it in the GUI but the data is there, Thanks!