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.
;WITH CTE AS (
SELECT DISTINCT
TOP (100) PERCENT dbo.setups_loanstatus.stat
dbo.loan_status.datefirste
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.stat
dbo.loan_status ON dbo.setups_loanstatus.stat
dbo.loan_main.loanrecordid
SELECT
*
FROM
CTE
WHERE
RN = 1