CODEWITHSUNDEEP
sqlms-access
Patrick
Patrick

Reputation: 7722

Access SQL Query Help Using Not Exists

I have a table named sys_event_log with the following columns:

user_name, event_type, event_message, time_stamp

Login and logout events are tracked with event_type of either "Login Success" or "Logout Event"

Using SQL in Access I'm trying to return a list of distinct users that have logged into the database that have not yet logged out based strictly on the event log event_types and time_stamps.

The query I have come up with so far is: 

SELECT DISTINCT
     a.user_name,
     a.event_type,
     a.time_stamp
FROM
     sys_event_log a
WHERE NOT EXISTS
 (
     SELECT
          1
     FROM
          sys_event_log b
     WHERE
          b.time_stamp > a.time_stamp 
          AND a.user_name = b.user_name 
          AND a.event_type = "Logout Event"
 )
AND a.event_type = "Login Success";

It feels close but it is not returning the expected dataset.

Upvotes: 0

Views: 5876

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

It will make more sense if you use

b.event_type = "Logout Event"

here

      b.time_stamp > a.time_stamp 
      AND a.user_name = b.user_name 
      AND a.event_type = "Logout Event"

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

  SELECT a.user_name
       , COUNT(*) AS logins
       , ( SELECT COUNT( b.time_stamp ) 
             FROM sys_event_log b 
             WHERE b.user_name = a.user_name
               AND b.event_type = "Logout Event"
          ) AS logouts
       , logins - logouts AS activeConnections
    FROM sys_event_log a
    WHERE a.event_type = "Login Success"
    GROUP BY a.user_name
    HAVING logins > logouts

Upvotes: 1

Alex W
Alex W

Reputation: 3373

Try this:

SELECT 
   a.user_name, 
   a.event_type, 
   a.time_stamp
FROM
   sys_event_log a
WHERE a.event_type = "Login Success" AND
   a.time_stamp = ( SELECT MAX( b.time_stamp ) 
                    FROM sys_event_log b 
                    WHERE b.user_name = a.user_name )

Upvotes: 1

Related Questions