MS SQL query to show nearest date

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] ROLES
LEFT JOIN
    [Database].[Schema2].[table1] Activity on ROLES.[USERID] = Activity.[SIGNON_USERID]
Order by ROLES.USERID

Open in new window


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
k3vsmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented: 
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] ROLES
LEFT JOIN [Database].[Schema2].[table1] Activity on ROLES.[USERID] = Activity.[SIGNON_USERID]
WHERE RN=1 Order by ROLES.USERID)D

Open in new window

Naitik GamitSoftware DeveloperCommented: 
SELECT  
    ROLES.[USERID],
    ROLES.[ROLE],
	MAX(Activity.[SIGNON_TIME_STAMP])
FROM    
[Database].[Schema1].[table1] ROLES
LEFT JOIN
    [Database].[Schema2].[table1] Activity on ROLES.[USERID] = Activity.[SIGNON_USERID]
GROUP BY ROLES.[USERID],ROLES.[ROLE]
Order by ROLES.USERID

Open in new window

PortletPaulEE Topic AdvisorCommented:
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] ROLES
LEFT 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] = 1
ORDER BY ROLES.USERID
;

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

LEARN MORE
k3vsmithAuthor Commented:
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?
PortletPaulEE Topic AdvisorCommented:
>>"Can I set this up as a view? "
Yes, absolutely. It will produce "current state" information when it is used.

I would not go down the create table rabbit hole.

So that we are clear:

>>" Or would that still only give me the info as of the time the view was created?"
NO! absolutely not. That is not what views do.

When you create a view it is the query CODE that gets "frozen in time", NOT the query result.

Did I mention? I would not go down the create table rabbit hole.

Oh, but one thing you never want to do in a view is use the final ORDER BY clause (this does not apply to the row_number calculation)

CREATE VIEW give_it_a_good_name_here AS

SELECT
      ROLES.[USERID]
    , ROLES.[ROLE]
    , Activity.[SIGNON_TIME_STAMP]
FROM [Database].[Schema1].[table1] ROLES
LEFT 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] = 1

Open in new window

/* ORDER BY ROLES.USERID  don't include THIS ROW */

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
k3vsmithAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.