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:
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] )