How to group in a left join

[Mark Bordelon]

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

[ck9663]

First, create your sample data.....

drop table session;

with test_data 
as
(
select cast('10/23/2010 8:21:46 AM' as datetime) as whenlogged,    10 as userid,    'mimmy (10) started session' as msg
union all
select '10/23/2010 8:07:21 AM',    12,    'mark (12) started session'
union all
select '10/22/2010 2:38:37 PM',    12,    'mark (12) started session'
union all
select '10/22/2010 2:27:21 PM',    32,    'grace (32) ended session'
union all
select '10/22/2010 2:11:01 PM',    32,    'grace (32) started session' 
) 

select 
*
into session
from test_data

select * from session

Here's the code that will summarize your session table. You're going to have to convert the datetime column to varchar if you really want your query to return "?" for the missing end session. This query defaults it to NULL, instead.

;with sorted_session
as 
(
   select 
   row_cnt = row_number() over(partition by userid order by whenlogged),
   whenlogged, userid, msg
   from session
) 
select 
logged_in.userid, logged_in.whenlogged as session_start, 
session_id = 
case 
   when logged_out.row_cnt is null then null
   else logged_out.whenlogged
end
from sorted_session logged_in
   left join sorted_session logged_out on logged_in.userid = logged_out.userid and logged_in.row_cnt + 1 = logged_out.row_cnt and logged_out.msg like '%ended session%'
where logged_in.msg like '%started%'

Just a suggestion, make the start/end session indicator as a code or a flag, not a string...

Happy Coding!!!


~~ CK