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:
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).
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'