Get max logins from user table by year

Question

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

Why are you using a `#tempTable` for what looks like persisted data? And why aren't you using a `date`-typed column? — Dai, Oct 10 '22 at 20:34
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Stu, Oct 10 '22 at 20:35

Paweł Dyl · Accepted Answer · 2022-10-11T07:36:58.790

0

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

If you need all users omit where clause and use user in partitioning:

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

edited Oct 11 '22 at 07:36

answered Oct 10 '22 at 20:30

Paweł Dyl

8,888
1
11
27

This works but only gives me one user even if I leave out the where clause – user13520940 Oct 10 '22 at 20:40
You would need to include the User in the window partition. – Stu Oct 10 '22 at 20:56
Answer updated to match question update :) – Paweł Dyl Oct 11 '22 at 07:37

Willi Jakob · Answer 2 · 2022-10-10T20:56:49.937

0

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'

edited Oct 10 '22 at 20:56

answered Oct 10 '22 at 20:52

Willi Jakob

29
4

Get max logins from user table by year

2 Answers2