Especially the rank part. Thanks!
if @doc in ('V01')
Begin
;WITH Base AS (
SELECT p.DOC,
p.Reg,
n.RegionAcronym,
p.FO,
p.CLMS,
DATEDIFF(DAY, MAX(p.filedate), GETDATE()) AS Age,
MAX(FileDate) AS FileDate,
o.mailingaddressstate AS DDS,
o.ddsofficecode AS DDSCode,
MIN(ddsrcpt) AS DDSReceipt,
DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
--WHEN min(p.fo) <> max(p.fo) and MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' ELSE MIN(p.Title) END AS title
FROM pendingdds AS p
JOIN offices.dbo.OfficeCodes AS d
ON d.officecode = p.doc
JOIN natdocfile AS n
ON n.doc = p.fo
JOIN offices.dbo.DoorsInfo AS o
ON o.officecode = p.fo
where p.doc in ('V01', 'S67', 'S41', 'S66') AND ( Datediff(DAY, filedate, Getdate()) > 300 )
GROUP BY p.fo,
p.Reg,
n.regionacronym,
p.DOC,
p.CLMSsn,
o.ddsofficecode,
o.mailingaddressstate
),
Ranked AS (
SELECT *,
-- return only the first rank which is 1
ROW_NUMBER() OVER(PARTITION BY clms ORDER BY FileDate DESC) AS rn
FROM Base)
SELECT *
FROM Ranked
WHERE rn = 1
ORDER BY clms, age DESC
end