Post reply

Get Most Recent Transaction per User

  • April 11, 2018 at 10:58 am

    #334207

    Hello,  I am looking to create  query to determine the most recent login date for each user.  Below is my sample code and expected results, any help is sincerely appreciated.


    -- DROP TABLE #UserLogon
    CREATE TABLE #UserLogon (RecID int IDENTITY(1,1), UserID int, LogonDt datetime)

    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (101, '02/01/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (101, '03/11/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (101, '04/10/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (102, '01/15/2017')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (102, '11/01/2017')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (102, '12/31/2017')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (103, '12/29/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (103, '01/01/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (104, '04/11/2018')

    -- SELECT * FROM #UserLogon

    -- Based on the sample code and desired results, I would like to following data to be returned back
    -- UserID | LogonDt
    -- 101  | 04/10/2018
    -- 102  | 11/01/2017
    -- 103  | 01/01/2018
    -- 104  | 04/11/2018

    Thank you in advance!

  • April 11, 2018 at 1:59 pm

    #1986475

    rjjh78 - Wednesday, April 11, 2018 10:58 AM

    Hello,  I am looking to create  query to determine the most recent login date for each user.  Below is my sample code and expected results, any help is sincerely appreciated.


    -- DROP TABLE #UserLogon
    CREATE TABLE #UserLogon (RecID int IDENTITY(1,1), UserID int, LogonDt datetime)

    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (101, '02/01/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (101, '03/11/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (101, '04/10/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (102, '01/15/2017')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (102, '11/01/2017')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (102, '12/31/2017')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (103, '12/29/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (103, '01/01/2018')
    INSERT INTO #UserLogon (UserID, LogonDt) VALUES (104, '04/11/2018')

    -- SELECT * FROM #UserLogon

    -- Based on the sample code and desired results, I would like to following data to be returned back
    -- UserID | LogonDt
    -- 101  | 04/10/2018
    -- 102  | 11/01/2017
    -- 103  | 01/01/2018
    -- 104  | 04/11/2018

    Thank you in advance!

    Normally you would just do something like:
    SELECT userid,
        MAX(LogonDt)
    FROM #UserLogon
    GROUP BY userid

    That's the max date for each login id but it doesn't match with what you list for expected results.
    If you dont want to include any with dates after today, you could add a where clause such as - WHERE LogonDt < GetDate()
    But that still doesn't have the same expected results at what you listed
    Maybe I'm missing something or some other requirement is missing or if your expected results in maybe not correct.
    I can't figure out how user 102 has an expected last login of 11/01/2017 when they also have one listed on 12/31/2017?

    Sue

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply