Skip to content
Advertisement

How do I get the most recent processdate in a group?

I’m wanting to get the most recent entry per month using acct.processdate. I’ve tried using the MAX() function, but it doesn’t help since I am using group by. How do I go about doing this?

DECLARE @StartDate AS INT
        , @EndDate AS int

SET @StartDate = '20190131'
SET @EndDate = '20191130'

SELECT count(*) as [Account Count]
        , datename(MONTH, CAST(CAST(ProcessDate AS VARCHAR(10)) AS SMALLDATETIME)) AS [Month Name]
        , (CAST(CAST(acct.ProcessDate AS VARCHAR(10)) AS SMALLDATETIME)) AS [Process Date]

from ARCUSYM000.dbo.ACCOUNT acct

where acct.CLOSEDATE is null AND 
 (acct.ProcessDate >= @StartDate
    AND acct.ProcessDate <= @EndDate)
GROUP BY acct.ProcessDate
ORDER BY (CAST(CAST(acct.ProcessDate AS VARCHAR(10)) AS SMALLDATETIME)) desc

This is my current result:

enter image description here

This is my desired result:

enter image description here

New result with the CTE:

enter image description here

Advertisement

Answer

You can put your result in a cte and use not exists function

DECLARE @StartDate AS INT
        , @EndDate AS int

SET @StartDate = '20190131'
SET @EndDate = '20191130'

;with cte as
(
SELECT count(*) as [Account Count]
        , datename(MONTH, CAST(CAST(ProcessDate AS VARCHAR(10)) AS SMALLDATETIME)) AS [Month Name]
        , (CAST(CAST(acct.ProcessDate AS VARCHAR(10)) AS SMALLDATETIME)) AS [Process Date]

from ARCUSYM000.dbo.ACCOUNT acct

where acct.CLOSEDATE is null AND 
 (acct.ProcessDate >= @StartDate
    AND acct.ProcessDate <= @EndDate)
GROUP BY acct.ProcessDate
)
select * from cte a
where not exists(select 1 from cte b where a.[Month Name] = b.[Month Name] and 
a.[Process Date] < b.[Process Date] )

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement