Tracking events

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

Thanks in advance for your help

I'm working with a database pulled from a commercial application.

This application records a variety of events associated (enter a room,
exit room, logon, logoff) with users, as well as some system events
(system reboot). What I'm trying to do is determine how many users
are in a room at a specific point in time, and in another instance,
how many distinct users have been in a room at any point during a
specific time period.

My Events table looks something like:

UserID, varchar(20)
EventDesc, varchar(20): ('Enter room', 'Exit room', 'Logoff', 'Logon',
'Reboot')
RoomID, int
EventTime, Date

My problem is that the version of the program whose data I am working
with did not elegantly close things out. It allowed users to logoff
without first generating events to Exit them from whatever room(s)
they were in, and whenever the system crashed and rebooted, it does
not generate transactions which exit the users from a room.

What I'd like the results of my query to look like is:

RoomID, UserID, EnteredRoom_dt, ExitedRoom_dt

Where the ExitedRoom_dt value is determined by the smaller of the
following three values:
1. first time the user exits the room after entering it
2. first time the user logged out after entering the room
3. first system reboot after the user entered the room

I've been able to implement this using procedural code in my ASP, but
know there has to be a quicker way to do it using a query.
 
Dale-

Fascinating problem. If you're doing this from ASP, you'll eventually need
to run this as a single query, but let's break it down into parts first.
You should consider putting this together as a stored procedure that you can
execute from ASP with user and date/time parameters. (I assume from your
table description that this is a table in SQL Server.) You're interested in
Enter, Exit, Logoff, and Reboot entries to solve this problem, so let's
extract those first.

vwEnter:
SELECT UserID, RoomID, EventTime
FROM Events
WHERE Events.EventDesc = 'Enter room';

vwExit:
SELECT UserID, RoomID, EventTime
FROM Events
WHERE Events.EventDesc = 'Exit room';

vwLogoff:
SELECT UserID, RoomID, EventTime
FROM Events
WHERE Events.EventDesc = 'Logoff';

vwReboot:
SELECT UserID, RoomID, EventTime
FROM Events
WHERE Events.EventDesc = 'Reboot';

Now, let's get all the enter events and find the next most proximate Exit,
Logoff, or Reboot:

SELECT TOP 100 PERCENT vwEnter.UserID, vwEnter.RoomID, vwEnter.EventTime As
EnterTime,
(SELECT Min(EventTime) FROM vwExit
WHERE vwExit.UserID = vwEnter.UserID
AND vwExit.EventTime >= vwEnter.EventTime) AS ExitTime,
(SELECT Min(EventTime) FROM vwLogoff
WHERE vwLogoff.UserID = vwEnter.UserID
AND vwLogoff.EventTime >= vwEnter.EventTime) AS LogoffTime,
(SELECT Min(EventTime) FROM vwReboot WHERE
vwReboot.UserID = vwEnter.UserID AND /* exclude this if Reboot records have
no UserID */
vwReboot.EventTime >= vwEnter.EventTime) As RebootTime
FROM vwEnter
ORDER BY vwEnter.UserID, vwEnter.EventTime

It's a simple matter to use the earlier of the Exit, Logoff, or Reboot in
each row to determine the actual exit time.

Note that the above might run faster as an INNER JOIN:

SELECT TOP 100 PERCENT vwEnter.UserID, vwEnter.RoomID, vwEnter.EventTime As
EnterTime,
vwExit.EventTime As ExitTime, vwLogoff.EventTime As LogoffTime,
vwReboot.EventTime As RebootTime
FROM vwEnter INNER JOIN vwExit
ON vwEnter.UserID = vwExit.UserID
AND vwEnter.EventTime <= vwExit.EventTime
INNER JOIN vwLogoff
ON vwEnter.UserID = vwLogoff.UserID
AND vwEnter.EventTime <= vwLogoff.EventTime
INNER JOIN vwReboot /* This join won't work if reboot has no UserID */
ON vwEnter.UserID = vwReboot.UserID
AND vwEnter.EventTime <= vwReboot.EventTime
WHERE vwExit.EventTime =
(SELECT Min(EventTime) FROM vwExit As E2
WHERE E2.UserID = vwEnter.UserID
AND E2.EventTime >= vwEnter.EventTime)
AND vwLogoff.EventTime =
(SELECT Min(EventTime) FROM vwLogoff As L2
WHERE L2.UserID = vwEnter.UserID
AND L2.EventTime >= vwEnter.EventTime)
AND vwReboot.EventTime =
(SELECT Min(EventTime) FROM vwReboot As R2 WHERE
R2.UserID = vwEnter.UserID AND /* exclude this if reboot records have no
userid */
R2.EventTime >= vwEnter.EventTime)
ORDER BY UserID, EnterTime

Have fun...
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Hi Dale,

Does this do the job?

SELECT e.RoomID, e.UserID, e.EventTime AS EnteredRoom_dt,
(SELECT MIN(EventTime) FROM Events eo
WHERE ((eo.RoomID = e.RoomID AND eo.UserID = e.UserID AND
eo.EventDesc = 'Exit room')
OR (eo.UserID = e.UserID AND eo.EventDesc = 'Logoff')
OR (eo.EventDesc = 'Reboot'))
AND eo.EventTime >= e.EventTime) AS ExitedRoom_dt
FROM Events e
WHERE e.EventDesc = 'Enter room'

If this doesn't work, please post your table structure and sample data (see
http://www.aspfaq.com/5006)
 
Jacco,

That looks like it might just work. I'll take that over to the
network where this data is located and give it a try. I'll let you
know how it worked out.

--
HTH

Dale Fye


Hi Dale,

Does this do the job?

SELECT e.RoomID, e.UserID, e.EventTime AS EnteredRoom_dt,
(SELECT MIN(EventTime) FROM Events eo
WHERE ((eo.RoomID = e.RoomID AND eo.UserID = e.UserID AND
eo.EventDesc = 'Exit room')
OR (eo.UserID = e.UserID AND eo.EventDesc = 'Logoff')
OR (eo.EventDesc = 'Reboot'))
AND eo.EventTime >= e.EventTime) AS ExitedRoom_dt
FROM Events e
WHERE e.EventDesc = 'Enter room'

If this doesn't work, please post your table structure and sample data
(see
http://www.aspfaq.com/5006)
 
Jacco,

Thanks. Just what the doctor ordered, and very elegant.

Unfortunately, during the testing process, I identified that some (not
all) of the room exit events also generate a logoff event. The bad
part about this is that because the user has the ability to be in two
rooms at once, when they log out of one of the rooms, the logoff event
will apply as the Min() of the EventTime for any of the other rooms
that are already open.

The good news is that these logoff events have the same time stamp as
the room exit events, so I think I can create a subquery that will
ignore the logoff events that have an associated room exit.

--
HTH

Dale Fye


Hi Dale,

Does this do the job?

SELECT e.RoomID, e.UserID, e.EventTime AS EnteredRoom_dt,
(SELECT MIN(EventTime) FROM Events eo
WHERE ((eo.RoomID = e.RoomID AND eo.UserID = e.UserID AND
eo.EventDesc = 'Exit room')
OR (eo.UserID = e.UserID AND eo.EventDesc = 'Logoff')
OR (eo.EventDesc = 'Reboot'))
AND eo.EventTime >= e.EventTime) AS ExitedRoom_dt
FROM Events e
WHERE e.EventDesc = 'Enter room'

If this doesn't work, please post your table structure and sample data
(see
http://www.aspfaq.com/5006)
 
Back
Top