Complex / Algorithmic SQL Query

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 nex开发者_StackOverflow中文版t 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).

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

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

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