Help with agent logged in time

[Revolution1200]

Hi, hopefully someone will be able to help with this.

I am trying to build up the total amount of time we have agents logged in grouped by 15 minute intervals.

The data returned from the table looks like this

TimeStamp AgentLogin Action seq
08:30:00 1000 LI 1
08:30:00 1001 LI 1
08:45:00 1002 LI 1
08:50:00 1002 LO 2

Where LI = Log in and LO = Log out

Unless the agent logs out there will be no LO entry for the agent.

For the above example if queried at 09:00:00 i am looking to return the following values

Time Total Logged in time
08:30:00 to 08:45:00 00:30:00
08:45:00 to 09:00:00 00:35:00 <- IE all agents were logged in at this point but 1 logged out after 5 minutes.

This is for an application written in VB6.

Thanks for any pointers.

Stuart

 

[RyanEK]

Hi,

Just a question... with your desired results, are they segmented in 45min increments by default?

Ryan

 

[RyanEK]

Sorry I mean 15 min increments.

 

[RyanEK]

To get you started... you could use the following sql to determine how long each Agent has been logged in based on a @time variable ie. 9:00am

declare @temp table (ts datetime, agent varchar(5), act varchar(2), seq int)
insert into @temp values ('2009-01-01 08:30:00','1000','LI',1)
insert into @temp values ('2009-01-01 08:30:00','1001','LI',1)
insert into @temp values ('2009-01-01 08:45:00','1002','LI',1)
insert into @temp values ('2009-01-01 08:50:00','1002','LO',2)

declare @time datetime
SET @time = '2009-01-01 09:00:00' 

select agent, datediff(mi, startTime, endtime) as totalmin
from ( 
  select t1.agent, t1.ts as StartTime,
  isnull((select t2.ts from @temp t2 
    where t2.agent = t1.agent and t2.ts > t1.ts 
    and t2.act = 'LO'),@time) as EndTime
  from @temp t1
  where act = 'LI' and ts < @time
) as t

 

[Revolution1200]

Hi RyanEK,

Thanks for the responses, the data in the table are not segmented into 15 minute intervals, so i could have an entry of 08:46:34.

I should also mention that i cannot make any changes in the database, i can only query.

Stuart