Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010

    Unanswered: left join grouping

    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?


  2. #2
    Join Date
    Dec 2008
    At work...
    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.

  3. #3
    Join Date
    Oct 2010

    only one session per user

    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.

  4. #4
    Join Date
    Dec 2008
    At work...
    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)

  5. #5
    Join Date
    Oct 2010

    Correct Answer!

    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!


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts