1

I am wondering if its possible to implement an SQL Query that will act like a sort of algorithm to calculate a certain figure for me based on the following table:

This is the initial query,

SELECT Activity, TimeOfAction, Requestor
FROM EventLog
WHERE Requestor = 0
ORDER BY Requestor, TimeOfAction;

And a sample of the data that is returned, 

Login   2010-05-28 15:52:50.590 0

Login   2010-05-28 15:52:50.873 0

Logout  2010-05-28 15:52:50.890 0

Logout  2010-05-28 16:22:57.983 0

Login   2010-05-29 11:29:36.967 0

Logout  2010-05-29 11:29:37.640 0

As you can see there are duplicate logins and logouts in this dataset. I need to calculate the length of a session by taking the FIRST login and LAST logout when there are duplicates. So the first session given the data above would be from, 

5-28 15:52:50.590 to 5-28 16:22:57.983

The algorithm is roughly,

1) Order a list of logins / logouts by username, then by time of action

2) If entry is a login, search for the next logout that is followed immediately by a login (to confirm it is the last logout of all duplicates)

3) Use first login and last logout to create a new session (length is logout time - login time)

4) Repeat

Currently I am just implementing this in code but was wondering if its even possible in SQL (I'm not too familiar with SQL).

Improve this question

3 Answers 3

Reset to default
2

Sure... try something like this.

select e1.Requestor, 
       e1.TimeOfAction as LoginTime, 
       (select min(ActivityTime)
        from EventLog where TimeOfAction > e1.TimeOfAction 
        and Activity = 'Logout') as LogoutTime
from EventLog e1
where e1.ActivityType = 'Login'
order by Requestor, LoginTime

Second Solution... See if this works better for ya.

select requestor,
    (select min(activitytime)
     from eventlog 
     where activitytime < e.activitytime
     and activity = 'Login' and e.activity = 'Logout') as LoginTime, 
    (select max(activitytime)
     from eventlog 
     where activitytime > e.activitytime
     and activity = 'Logout' and e.activity = 'Login') as LogoutTime, 
from eventlog e
order by requestor, logintime
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, this is pretty close. The only problem is that the part min(TimeOfAction) from EventLog where TimeOfAction > e1.TimeOfAction and Activity = 'Logout' returns the first next logout. If there are duplicate logouts I need the last one of the duplicates, but obviously changing min to max would just get the largest logout in the entire dataset.
Is it possible to encapsulate in a where clause the logic of "next logout that is followed by a login" not only "next logout that is greater than the current Login record"? Because I think it has to be both. Since if there are duplicate logouts (not just duplicate logins) I need to grab the LAST logout of those duplicates rather than just the next one in the set. IE if we have: Login Logout Logout (duplicate logouts)
True. Changing it to max() would not work in this case. The query simply matches up each Login record to a Logout record. If I'm understanding you correctly, you want the last Logout record just before another Login record because multiple Logouts can occur? This doesn't quite make sense to me, how can a user Logout more than once without Logging in again?
Its a bug in the logging system, which is being worked on but not fixed yet so this is a temporary solution.
1 
select min(TimeOfAction) Login, null Logout, Requestor
  from EventLog
 where Activity = 'Login'
 group by Requestor
union
select null Login, max(TimeOfAction) Logout, Requestor
  from EventLog
 where Activity = 'Logout'
 group by Requestor
Improve this answer

1 Comment

I tried something similar and realized that it would only return one row per requestor. I think the report would need to contain each login-logout record per requestor.
0

Heres an option for you using some CTEs and row_numbers. Basically, it orders the events for each user, then finds the list of logins that either follow a logout or nothing, then finds a list of logouts that proceed logins or nothing, then associates them into pairs.

;with events as (
  select *,
         row_number() over(partition by Requestor order by TimeOfAction) row
  from EventLog
), logins as (
  select e1.Activity, e1.TimeOfAction, e1.Requestor,
         row_number() over(partition by e1.Requestor order by e1.TimeOfAction) row
  from events e1
    left join events e2 on e1.Requestor=e2.Requestor
                       and e1.row=e2.row+1
  where e1.Activity='Login'
    and e1.Activity!=isnull(e2.Activity, 'Logout')
), logouts as (
  select e1.Activity, e1.TimeOfAction, e1.Requestor,
         row_number() over(partition by e1.Requestor order by e1.TimeOfAction) row
  from events e1
    left join events e2 on e1.Requestor=e2.Requestor
                       and e1.row=e2.row-1
  where e1.Activity='Logout'
    and e1.Activity!=isnull(e2.Activity, 'Login')
)
select i.Requestor, i.TimeOfAction as loginTime, o.TimeOfAction as logoutTime
from logins i
  left join logouts o on i.Requestor=o.Requestor
                     and i.row=o.row

NOTE: Query performance may be (drastically?) increased by splitting off some, or all, of the CTE queries into temp tables. i.e. something like the following:

select *,
       row_number() over(partition by Requestor order by TimeOfAction) row
into #events
from EventLog

select e1.Activity, e1.TimeOfAction, e1.Requestor,
       row_number() over(partition by e1.Requestor order by e1.TimeOfAction) row
into #logins
from #events e1
  left join #events e2 on e1.Requestor=e2.Requestor
                     and e1.row=e2.row+1
where e1.Activity='Login'
  and e1.Activity!=isnull(e2.Activity, 'Logout')

select e1.Activity, e1.TimeOfAction, e1.Requestor,
       row_number() over(partition by e1.Requestor order by e1.TimeOfAction) row
into #logouts
from #events e1
  left join #events e2 on e1.Requestor=e2.Requestor
                     and e1.row=e2.row-1
where e1.Activity='Logout'
  and e1.Activity!=isnull(e2.Activity, 'Login')

select i.Requestor, i.TimeOfAction as loginTime, o.TimeOfAction as logoutTime
from #logins i
  left join #logouts o on i.Requestor=o.Requestor
                      and i.row=o.row

drop table #logouts
drop table #logins
drop table #events
Improve this answer

5 Comments

This works great, surprisingly fast too. Not exactly sure what CTE / events are but I'll look into that. Thanks.
No problem. CTE (Common Table Expressions) is essentially the with syntax for separating out sub-queries and only typing them once... at least that's all it's really doing here, but it can do other stuff too (like recursive queries).
I'm trying to convert this to use temp tables as you said. Would I basically be using SELECT INTO #temptable syntax for each table instead of the WITH keyword, where everything else is largely similar? I get an error 'Invalid object name' when trying to do FROM on a temp table I just created.
@sean, I've edited my answer to include a possible method of utilizing temp tables instead of CTEs. With the sample table I put together, the actual query plan's percentage is slightly better... but that doesn't mean much on sub-second queries...
Getting a few errors like 'Invalid object name #events' when doing the from on #events, Also 'The multi-part identifier' could not be bound anytime it says e1.(column). It seems that the tables are being dropped as soon as they are created -- the drop table commands show errors as well b/c the tables dont exist. I read around the net and it says this may be a permissions issue or a problem with the temp table scopes. Is there a setting I need to change? I am using SQL server management studio w/ SQL Express 2008 R2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.