Hola, forum. Not a SQL newbie here but new to the forum. A somewhat advanced query has my stumped. I have a table of session information that provides log-ons and log-off times like this (column names are whenlogged, userid, msg):

10/23/2010-8:21:46-AM 10 mimmy (10) started session
10/23/2010-8:07:21-AM 12 mark (12) started session
10/22/2010-2:38:37-PM 12 mark (12) started session
10/22/2010-2:27:21-PM 32 grace (32) ended session
10/22/2010-2:11:01-PM 32 grace (32) started session

I need to generate a report showing the session log-ons and log-offs, ordered by desc log-on date/time, something like this:

userid session start session end
10 10/23/2010-8:21:46-AM ?
12 10/23/2010-8:07:21-AM ?
12 10/23/2010-2:38:37-PM ?
32 10/23/2010-2:11:01-PM 10/22/2010-2:27:21-PM

I am stuck at this left-join:

select starts.userid,master.accountfullname(starts.userid ) as "user",starts.whenlogged as started, stops.whenlogged as stopped
from master.sessionlog starts left join master.sessionlog stops
on starts.userid=stops.userid and starts.whenlogged<stops.whenlogged
where starts.msgtype=0 and starts.userid<>0 and starts.msg like '% started session'
and stops.msgtype=0 and stops.userid<>0 and stops.msg like '% ended session'
order by starts.whenlogged desc

...since I cannot figure how to group the users in such a way as to take the closest log-off time after the log-on time for each user. The query should also gracefully handle the many cases where a user does not properly end the session (shown as ? in a report).

Can any SQL guru out there help?

Thanks,
Mark

How come you've decided to always "take the closest log-off time after the log-on time for each user"?

Isn't Con1 -> Con2 -> Discon1 -> Discon2
just as possible as Con1 -> Con2 -> Discon2 -> Discon1?

If the same user can have multiple open connections, I believe you'll need to distinguish between those. When you can do that, your query will be simple.

There can only be one session per user at a time, so
user1con user1con
cannot occur.
However, user1con can occur without there ever being user1discon, since a user can simply close a browser.

OK, that makes sense!

Try something like this:

SELECT userid, timestamp AS connecttime, whatevercolumn ,
(SELECT MIN(timestamp) FROM tab AS t2
WHERE t1.userid = t2.userid
AND t2.timestamp > t1.timestamp
AND t2.connectiontype = 'disconnect') AS disconnecttime
FROM tab AS t1
WHERE connectiontype = 'connect'


(Correlated subquery)

Tremendous. With some alterations, that does the trick.
So one can reference the outertable's vars in an inner nested query.
I did not know!

:-)