none
MAX(Date) from Multiple Tables RRS feed

  • Question

  • Hi,

    I need to create a query which I'm really struggling with....My SQL skills aren't that great!!  Basically I'd like to find out how I can see when a user was last active.

    I have a User table which stores the users basic information such as userid, username, first name, last name etc.  However, there are many other tables such as User_Permission, User_Message, User_Notification, User_Report, User_Role where the user could of been active/table updated.  All the tables have a datetimestamp.

    I'd like to output the username from Users table in one column and then have another column showing one MAX(date) from all the other tables. 

    It sounds so simple but I'm having a nightmare trying to get this in SSMS :(

    Any help is appreciated!

    Many Thanks,

    Brad

    Tuesday, February 26, 2019 9:10 AM

Answers

  • Without schema this is the best I can provide you with:

    select * from ( select tdt.UserID , max(tdt.DateStamp) as DateStampMax from ( select UserID, DateStamp from dbo.User_Permission union all select UserID, DateStamp from dbo.User_Message ) tdt

    group by tdt.UserID ) t inner join dbo.User u on t.UserID = u.UserID



    HTH, Regards, Dean Savović, www.tuneit.hr, www.savovic.com.hr


    Tuesday, February 26, 2019 9:27 AM
  • SELECT U.username, MAX(date) AS Date FROM (
    SELECT userId, MAX(date) AS date FROM User_Permission
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Message
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Notification
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Report
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Role
    GROUP BY userId
    ) A
    INNER JOIN User_Table U ON U.userId=A.UserId
    GROUP BY U.username


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by Brad Sherwin Tuesday, March 5, 2019 10:11 AM
    Tuesday, February 26, 2019 9:29 AM
  • SELECT  t.userid, MAX(dtupdated) dtupdated_max     
    
    FROM (
    Select u.userid, 
    t1.dtupdated as dtupdated1
    ,t2.dtupdated as dtupdated2
    ,t3.dtupdated as dtupdated3
    from [User] u 
    join [Test1] t1 on u.userid=t1.userid
    join [Test2] t2 on u.userid=t2.userid
    join [Test3] t3 on u.userid=t3.userid
    ) t
    cross apply (VALUES (dtupdated1,userid)
                         ,(dtupdated2,userid)
    					 ,(dtupdated3,userid)
    ) AS value(dtupdated,userid)
    Group by t.userid

    • Marked as answer by Brad Sherwin Tuesday, March 5, 2019 10:11 AM
    Tuesday, February 26, 2019 4:30 PM
    Moderator

All replies

  • Without schema this is the best I can provide you with:

    select * from ( select tdt.UserID , max(tdt.DateStamp) as DateStampMax from ( select UserID, DateStamp from dbo.User_Permission union all select UserID, DateStamp from dbo.User_Message ) tdt

    group by tdt.UserID ) t inner join dbo.User u on t.UserID = u.UserID



    HTH, Regards, Dean Savović, www.tuneit.hr, www.savovic.com.hr


    Tuesday, February 26, 2019 9:27 AM
  • SELECT U.username, MAX(date) AS Date FROM (
    SELECT userId, MAX(date) AS date FROM User_Permission
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Message
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Notification
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Report
    GROUP BY userId
    UNION ALL
    SELECT userId, MAX(date) AS date FROM User_Role
    GROUP BY userId
    ) A
    INNER JOIN User_Table U ON U.userId=A.UserId
    GROUP BY U.username


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by Brad Sherwin Tuesday, March 5, 2019 10:11 AM
    Tuesday, February 26, 2019 9:29 AM
  • SELECT  t.userid, MAX(dtupdated) dtupdated_max     
    
    FROM (
    Select u.userid, 
    t1.dtupdated as dtupdated1
    ,t2.dtupdated as dtupdated2
    ,t3.dtupdated as dtupdated3
    from [User] u 
    join [Test1] t1 on u.userid=t1.userid
    join [Test2] t2 on u.userid=t2.userid
    join [Test3] t3 on u.userid=t3.userid
    ) t
    cross apply (VALUES (dtupdated1,userid)
                         ,(dtupdated2,userid)
    					 ,(dtupdated3,userid)
    ) AS value(dtupdated,userid)
    Group by t.userid

    • Marked as answer by Brad Sherwin Tuesday, March 5, 2019 10:11 AM
    Tuesday, February 26, 2019 4:30 PM
    Moderator