I have a query that selects users and login times. I only care about the latest login, not all of there logins.
My current query is showing all of there logins.
SELECT ROLES.[USERID], ROLES.[ROLE], Activity.[SIGNON_TIME_STAMP]FROM [Database].[Schema1].[table1] ROLESLEFT JOIN [Database].[Schema2].[table1] Activity on ROLES.[USERID] = Activity.[SIGNON_USERID]Order by ROLES.USERID
I tried using GETDATE() but may not be using it correctly.
I added a WHERE clause
WHERE Activity.[SIGNON_TIME_STAMP] > GETDATE()
but that just gives me same thing well minus about 50 rows?
Id like to see
userA role Lastlogindate
but am seeing
userA role logindate1
userA role logindate2
userA role logindate3
userA role logindate4
logindate (SIGNON_TIME_STAMP) should be either todays date or closest to it.
I should also mention the SIGNON_TIME_STAMP is formatted as such: 2017-03-17 15:21:00.460
Please help
Microsoft SQL ServerSQL
Last Comment
k3vsmith
8/22/2022 - Mon
Mike Eghtebas
SELECT * FROM (Select ROLES.[USERID], ROLES.[ROLE], ROW_NUMBER() OVER(PARTITION BY ROLES.[USERID] ORDER BY Activity.[SIGNON_TIME_STAMP] Desc) RN From [Database].[Schema1].[table1] ROLESLEFT JOIN [Database].[Schema2].[table1] Activity on ROLES.[USERID] = Activity.[SIGNON_USERID]WHERE RN=1 Order by ROLES.USERID)D
If you only need the one date column from that activity table then using group by and max() should be fine. ROW_NUMBER() OVER() is my usual goto solution for this because it gives you access to complete rows of data unaffected by aggregate functions.
Here however I'm not sure why you are using a LEFT JOIN, perhaps to list users who have never logged on?
If you really need that to be effective you must table care the where clause does not inhibit rows without a join from the result.
Below I have used row_number() prior to the join and it allows the result to return unjoined users.
SELECT ROLES.[USERID] , ROLES.[ROLE] , Activity.[SIGNON_TIME_STAMP]FROM [Database].[Schema1].[table1] ROLESLEFT JOIN ( SELECT [SIGNON_USERID] , [SIGNON_TIME_STAMP] , ROW_NUMBER() OVER (PARTITION BY [SIGNON_USERID] ORDER BY [SIGNON_TIME_STAMP] DESC) RN FROM [Database].[Schema2].[table1] ) Activity ON ROLES.[USERID] = Activity.[SIGNON_USERID] AND Activity.[RN] = 1ORDER BY ROLES.USERID;
Naitik Gamit and PortletPaul - Your guys solutions are what I'm after. Thanks! Mike - I couldnt get yours to work.
And to answer PortletPaul, yes I also want to see users who have not logged in -reason for the Join.
So now I need to either create a view from this select and grant to a user or create a table from this select and grant to a user.
I suspect if I create a table, I'd have to set it up as a job, to drop table, recreate table and give grant possibly nightly...
Can I set this up as a view? Or would that still only give me the info as of the time the view was created?
User (granted user) wants to be able to come to this view/table any day and see who has what roles and the last time they have logged in.
How best to handle?
Open in new window