Get max logins from user table by year-CodePudding

I have a table like this

CREATE TABLE #temptable 
(
    [Year] int, 
    [Month] int, 
    [User] varchar(255), 
    [Logins] int 
)

INSERT INTO #temptable ([Year], [Month], [User], [Logins])
VALUES (2021, 12, 'Jon', 4), 
       (2021, 12, 'Fred', 5), 
       (2021, 7, 'Jon', 1), 
       (2021, 7, 'Fred', 3), 
       (2022, 12, 'Jon', 9), 
       (2022, 12, 'Fred', 7), 
       (2022, 7, 'Jon', 5), 
       (2022, 7, 'Fred', 1)

I need to write a query that returns the last month and the logins for that user. The count is cumulative so I just need the latest month value for each year

So for this table for fred it would be

2021, fred, 12, 5
2022, fred, 12, 7
2021, Jon, 12,4
2022, Jon, 12,9

CodePudding user response：

I would use following query:

DECLARE @user varchar(255) = 'Fred'

SELECT [Year], [Month], [User], [Logins]
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY [Month] DESC) N
    FROM #temptable
    WHERE [User]=@user
) T
WHERE N=1

CodePudding user response：

WITH cte as (
    SELECT Year, Month, [User], Sum(Logins) SumLogins
        , ROW_NUMBER() OVER (PARTITION BY Year, [User] ORDER BY Month DESC) RowNumber
    FROM #temptable
    GROUP BY Year, Month, [User]
)
SELECT Year, Month, [User], SumLogins
FROM cte
WHERE Rownumber=1
AND [User] = 'Fred'