left join query problem

[markcbordelon]

Hey 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

[skhanal]

You may have to use some tricks to get them. I found Itzik's methods useful for such queries. See if he had solutions in his website or sqlmag websites.

http://www.sql.co.il/

http://www.sqlmag.com