Solved

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

Posted on 2012-12-28

1 solution

Medium Priority

320 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.

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

Join The Community

6 Comments

LVL 15

Expert Comment

by:tim_cs

ID: 387275482012-12-28

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

Author Comment

by:ISBTECH

ID: 387282032012-12-28

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?

LVL 15

Expert Comment

by:tim_cs

ID: 387282092012-12-28

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

Author Comment

by:ISBTECH

ID: 387282382012-12-28

Strange, still crashes management studio as a view but works fine as a query.

LVL 15

Accepted Solution

by:tim_cs

tim_cs earned 2000 total points

ID: 387282872012-12-28

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

Author Closing Comment

by:ISBTECH

ID: 387282912012-12-28

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

Featured Post

Question has a verified solution.

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

Cloud+Hadoop – A Boom To Cloud Market; Know What AWS & Azure Has To Contribute

Article by: Shakshi

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.

SSAS: Automating SQL Server Agent service logon as administrator

Article by: Dorababu

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…

Executing a SQL Server Function from Within Access

Video by: TechMommy

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…