SQL How to find max date grouped by month where a value appears-CodePudding

I have a table that contains a date column, a code column, and an count column. Each day the table is updated and if there is something in the code column to capture, it is recorded. However, not every code has something to record each day, so I would like to see the latest record for each code in each month. I am wondering if there is an aggregate function or something else that I can do to achieve this. Right now I have this:

SELECT 
    MAX(date) as 'date'
    ,CONCAT(CAST(YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0)) AS VARCHAR(10)), ' ', RIGHT('00'   CONVERT(NVARCHAR(2), DATEPART(MONTH, date)), 2)) AS 'month'
    ,code
    ,count
FROM counttable
WHERE 
    area = 'total'
    AND type = 'cost' 
GROUP BY 
    CONCAT(CAST(YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0)) AS VARCHAR(10)), ' ', RIGHT('00'   CONVERT(NVARCHAR(2), DATEPART(MONTH, date)), 2)) 
    ,code
    ,count
ORDER BY 1, 3

This isn't working I think because there can be different counts for the same code throughout the month. I can't do the MAX() count either as the count can down as well as up throughout the month. How can I go about getting the last value grouped by month or any other date grouping larger than a day.

Example of what I am trying to get:

|date       | month    | code | count
|01/27/2022 |  2022 01 |  PT  |   7
|01/31/2022 |  2022 01 |  CR  |   15
|02/28/2022 |  2022 02 |  PT  |   9
|02/28/2022 |  2022 02 |  CR  |   19

So the last date for each code may not be the last day of the month, as seen in the first row. So if that is the case I want to get that count as that is the last day the code actually appears in the month.

CodePudding user response：

I suspect you are looking for something like:

SELECT
 date
 , month
 , code
 , count
FROM
(
SELECT 
    date
    , FORMAT(date, 'yyyy MM') 'month'
    , code
    , count
    , row_number() OVER(PARTITION BY code, FORMAT(date, 'yyyy MM') ORDER BY date DESC) rn
FROM counttable
WHERE 
    area = 'total'
    AND type = 'cost'
)
WHERE rn = 1 
ORDER BY 1, 3

The inner query uses row_number to mark the row with the most recent date (because of ORDER BY date DESC) for each code and month (because of the PARTITION BY) as 1. The outer query grabs the rows marked rn=1 (and removes the rn column).

So for each code and month you end up with the maximum date and the count associated with that maximum date.

Although it's separate from your question, I changed your month calculation to FORMAT(date, 'yyyy MM'). This seems much cleaner to me and, unless I'm misunderstanding something about your existing code, it's equivalent. The above query would work with your existing month calculation if there's a reason to prefer that.