How to get this logic in SQL Query?

Question

I have two tables to track user activity:

I need to write a SQL query to get data in below format

: UserId|UserRole|LoginTime|LogOutTime

Note : LoginTime/LogOutTime is Activity_Time_Stamp in ActivityAuditTrail table. I am not able to figure out a logic for this.

Answer 1

SELECT User_Id, User_Role, Activity_Time_Stamp AS LoginTime, 
( SELECT Activity_Time_Stamp 
  FROM ActivityAuditTrail aud2
  WHERE aud2.Activity_Id=2 
  AND ActivityAuditTrail.User_Id=aud2.User_Id 
  AND ActivityAuditTrail.User_Session=aud2.User_Session 
) AS LogOutTime
FROM ActivityAuditTrail
WHERE Activity_Id=1

I'm being on the safe side and assuming that sessions are not unique to a user, but if they are you may be able to leave out the user_id from the WHERE entirely.