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?
x
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:
This is my desired result:
New result with the CTE:
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] )