x

How many minutes is agent ready by determined interval

On the 18th March I asked the question How many minutes agent is logged in per interval and [WilliamD][2] was able to provide me with a fantastic answer.

As you would expect that was great but the requirements have changed (Who would have thought!) and now the customer (The person I am doing the work for - this isn't paid work) would like to see how long a particular agent is ready to receive calls by interval.

We use the Cisco ICM/IPCC dialer so I am able to look at the Agent Event table and produce the following data for one agent:

SkillTargetID EventID     EventDateTime           EventDuration
------------- ----------- ----------------------- -------------
52163         1           2011-03-20 09:59:50.000 0
52163         3           2011-03-20 12:01:11.000 7281
52163         3           2011-03-20 13:26:39.003 3153
52163         3           2011-03-20 13:43:55.000 1035
52163         3           2011-03-20 15:08:56.000 2647
52163         3           2011-03-20 15:23:54.000 149
52163         3           2011-03-20 15:52:27.000 225
52163         3           2011-03-20 15:57:52.000 193
52163         3           2011-03-20 16:00:06.003 0
52163         2           2011-03-20 16:00:06.010 21616

An EventID of 1 is a login, 2 is logout and 3 is not ready. However the not readies are logged at the end of the event and the duration - datetime gives you the start time so I did a script that produces an example set of data for one agent.

IF OBJECT_ID('tempdb..#AgentEventLog') IS NOT NULL DROP TABLE #AgentEventLog

SELECT 12345 [SkillTargetID]
, 1 [EventID]
, '2011-03-20 09:59:50.000' [EventDateTime]
, 0 [EventDuration]
, CONVERT(datetime, NULL) [EventStartDateTime]
INTO #AgentEventLog
UNION ALL 
SELECT 12345, 3, '2011-03-20 12:01:11.000', 7281, NULL
UNION ALL
SELECT 12345, 3, '2011-03-20 13:26:39.003', 3153, NULL
UNION ALL
SELECT 12345, 3, '2011-03-20 13:43:55.000', 1035, NULL
UNION ALL
SELECT 12345, 3, '2011-03-20 15:08:56.000', 2647, NULL
UNION ALL
SELECT 12345, 3, '2011-03-20 15:23:54.000', 149, NULL
UNION ALL
SELECT 12345, 3, '2011-03-20 15:52:27.000', 225, NULL
UNION ALL
SELECT 12345, 3, '2011-03-20 15:57:52.000', 193, NULL
UNION ALL
SELECT 12345, 3, '2011-03-20 16:00:06.003', 0, NULL
UNION ALL
SELECT 12345, 2, '2011-03-20 16:00:06.010', 21616, NULL

UPDATE #AgentEventLog
SET EventStartDateTime = DATEADD(s, - EventDuration, EventDateTime)

SELECT * FROM #AgentEventLog

IF OBJECT_ID('tempdb..#AgentEventLog') IS NOT NULL DROP TABLE #AgentEventLog

This gives a table that looks like so:

SkillTargetID EventID     EventDateTime           EventDuration EventStartDateTime
------------- ----------- ----------------------- ------------- -----------------------
12345         1           2011-03-20 09:59:50.000 0             2011-03-20 09:59:50.000
12345         3           2011-03-20 12:01:11.000 7281          2011-03-20 09:59:50.000
12345         3           2011-03-20 13:26:39.003 3153          2011-03-20 12:34:06.003
12345         3           2011-03-20 13:43:55.000 1035          2011-03-20 13:26:40.000
12345         3           2011-03-20 15:08:56.000 2647          2011-03-20 14:24:49.000
12345         3           2011-03-20 15:23:54.000 149           2011-03-20 15:21:25.000
12345         3           2011-03-20 15:52:27.000 225           2011-03-20 15:48:42.000
12345         3           2011-03-20 15:57:52.000 193           2011-03-20 15:54:39.000
12345         3           2011-03-20 16:00:06.003 0             2011-03-20 16:00:06.003
12345         2           2011-03-20 16:00:06.010 21616         2011-03-20 09:59:50.010

What I would like is one line per agent with intervals along the top split by 30 minutes (or another value like the other question but only to show if an agent is ready to receive calls. I'm a bit stumped by this can anybody help?

Edit 1: Adding an example output table

SkillTargetID EventInterval           ReadyMinutes
------------- ----------------------- ------------
12345         2011-03-20 07:00:00.000 4
12345         2011-03-20 07:30:00.000 30
12345         2011-03-20 08:00:00.000 14
12345         2011-03-20 08:30:00.000 12
12345         2011-03-20 09:00:00.000 24
12345         2011-03-20 09:30:00.000 12
12345         2011-03-20 10:00:00.000 9
[2]: http://ask.sqlservercentral.com/users/256/williamd/
more ▼

asked Mar 21, 2011 at 11:06 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

I appreciate it needs a dynamic pivot to get what I need. That isn't what I need for now I can sort that out. What I need is a row for each interval with the number of minutes that agent was ready in that specific interval.
Mar 21, 2011 at 11:09 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Ian,

the way you are explaining the problem is very similar to your last question. You only need to change the table structure of my answer to fit your new requirements. Unfortunately, your sample data and expected results do not match. The sample data will return SkillTargetID 12345 as being ready for the whole day from 10am onwards.

The CASE statement inside the AgentTimings CTE needs restating to take into account that you have differing statuses deriving different calculations. The join to the AgentEventLog also needs a similar join condition to the CASE statement.

Other than these two changes, everything else should work. Here is my solution:

/* Test Data */
IF EXISTS ( SELECT  *
            FROM    sys.tables
            WHERE   name = 'AgentEventLog'
                    AND type = 'U' ) 
    BEGIN
        DROP TABLE dbo.AgentEventLog
    END

SELECT 12345 [SkillTargetID], 1 [EventID], '2011-03-20 09:59:50.000' [EventDateTime], 0 [EventDuration], CONVERT(datetime, NULL) [EventStartDateTime] INTO dbo.AgentEventLog UNION ALL 
SELECT 12345, 3, '2011-03-20 12:01:11.000', 7281, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 13:26:39.003', 3153, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 13:43:55.000', 1035, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:08:56.000', 2647, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:23:54.000', 149, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:52:27.000', 225, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:57:52.000', 193, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 16:00:06.003', 0, NULL UNION ALL
SELECT 12345, 2, '2011-03-20 16:00:06.010', 21616, NULL

UPDATE dbo.AgentEventLog
SET EventStartDateTime = DATEADD(s, - EventDuration, EventDateTime)

DECLARE @StartOfDay datetime,
    @EndofDay datetime,
    @NumberOfMinutes int,
    @MinuteGroupings smallint

SELECT  @StartOfDay = '2011-03-20 00:00:00',
        @EndOfDay = '2011-03-20 23:59:59',
        @MinuteGroupings = 30

/* Real Work! */
;
WITH    Timings
          -- Used to create every minute of a day (1440 minutes in 24 hours) using a Numbers table (function Nums here).
          -- The Timing groups are then identified, so that we can sum the minutes per grouping 
          AS (SELECT    Timings,
                        DATEADD(MINUTE, @MinuteGroupings * (ROW_NUMBER() OVER (PARTITION BY timingMinutes ORDER BY Timings) - 1), (DATEADD(day, DATEDIFF(day, 0, @StartOfDay), 0))) timingGrp -- Create the groups
              FROM      (SELECT DATEADD(minute, (n - 1), (DATEADD(day, DATEDIFF(day, 0, @StartOfDay), 0))) Timings,
                                (n - 1) % @MinuteGroupings timingMinutes -- used to make the groups - Uses MinuteGroupings to create that.
                         FROM   dbo.Nums(DATEDIFF(MINUTE, @StartOfDay, @EndOfDay)) N) N), -- Create the minutes between start and end of day
    AgentTimings
      -- We make the relation between actual working time and the timing groups previously identified  
      -- CASE statement identifies the minutes that the agent is logged in, skipping where they are on a lunch break
      AS (SELECT    *,
                    CASE WHEN T.Timings BETWEEN A.EventStartDateTime
                                        AND     DATEADD(s,EventDuration,EventStartDateTime)
                                        AND EventId IN (1,2)
                              THEN 1
                         ELSE 0
                    END WorkingTime,
                    CASE WHEN T.Timings BETWEEN A.EventStartDateTime
                                        AND     DATEADD(s,EventDuration,EventStartDateTime)
                                        AND EventId IN (3)
                              THEN 1
                         ELSE 0
                    END                         BusyTime
          FROM      Timings T
          LEFT JOIN dbo.AgentEventLog A ON T.Timings BETWEEN A.EventStartDateTime
                                              AND    DATEADD(s,EventDuration,EventStartDateTime))
SELECT  SkillTargetID ,
        TimingGrp,
        SUM(WorkingTime) - SUM(BusyTime) AvailableMinutes
FROM    AgentTimings
WHERE SkillTargetID  IS NOT NULL
GROUP BY SkillTargetID ,
        TimingGrp

Notice that the only change is the test table definition and the definition of the AgentTimings CTE, everything else remains the same.

You will still need the PIVOT, but you have stated that is not a problem. Give this a try and let me know.
more ▼

answered Mar 22, 2011 at 01:04 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

I didn't get the results I was expecting. When there is an EventID of 3 the EventStartDateTime is when they went into not ready and the EventDateTime is when they come out of that period of not ready. If they were in this state for 10 minutes then that interval needs to only show 20 minutes that they were ready for and take off the 10 minutes that they were not ready.
Mar 22, 2011 at 01:34 AM Ian Roke

OK, answer edited.

The AgentTimingsCTE now looks at when the user is working and when not, the final select takes the sum or available time minus the busy time to give the final amount of available minutes per interval.
Mar 22, 2011 at 02:01 AM WilliamD
When I ran this to start with I didn't believe the result because it had zeros in! However when I started to look at the data I provided it matches it spot on. Thanks for your help with this William you have been a lifesaver! :o)
Mar 22, 2011 at 02:30 AM Ian Roke
No probs - this is the sort of thing that gets the brain working. I rember you asked a similar question in the past, did that one get sorted out in the end?
Mar 22, 2011 at 02:32 AM WilliamD
I'm not sure let me revisit it.
Mar 22, 2011 at 05:24 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left

It looks to me like you're after the time spans that are implicit between your result rows. This gets those spans (in seconds rather than minutes):

;WITH source AS 
(SELECT ROW_NUMBER() OVER( order BY EventDateTime) AS rownumber, * FROM #event_list)

SELECT CASE WHEN s2.eventID = 3 THEN 
DATEDIFF(ss, s1.EventDateTime, CASE WHEN s2.EventStartDateTime > s1.EventDateTime THEN s2.EventStartDateTime ELSE s1.EventDateTime END) 
ELSE 0 END
AS availability_duration, s1.EventDateTime AS availability_start, s2.EventStartDateTime AS availability_end 
FROM source AS s1 INNER JOIN source AS s2 ON s2.rownumber = s1.rownumber + 1
WHERE s2.EventID <> 2

This query returns the following results:

availability_duration availability_start      availability_end
--------------------- ----------------------- -----------------------
0                     2011-03-20 09:59:50.000 2011-03-20 09:59:50.000
1975                  2011-03-20 12:01:11.000 2011-03-20 12:34:06.003
1                     2011-03-20 13:26:39.003 2011-03-20 13:26:40.000
2454                  2011-03-20 13:43:55.000 2011-03-20 14:24:49.000
749                   2011-03-20 15:08:56.000 2011-03-20 15:21:25.000
1488                  2011-03-20 15:23:54.000 2011-03-20 15:48:42.000
132                   2011-03-20 15:52:27.000 2011-03-20 15:54:39.000
134                   2011-03-20 15:57:52.000 2011-03-20 16:00:06.003

Here is the table setup

CREATE TABLE #event_list (
SkillTargetID INT,
EventID INT,
EventDateTime DATETIME,
EventDuration INT,
EventStartDateTime DATETIME
)
INSERT #event_list
SELECT 12345,         1,           '2011-03-20 09:59:50.000', 0,             '2011-03-20 09:59:50.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 12:01:11.000', 7281,          '2011-03-20 09:59:50.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 13:26:39.003', 3153,          '2011-03-20 12:34:06.003'
UNION ALL SELECT 12345,         3,           '2011-03-20 13:43:55.000', 1035,          '2011-03-20 13:26:40.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:08:56.000', 2647,          '2011-03-20 14:24:49.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:23:54.000', 149,          '2011-03-20 15:21:25.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:52:27.000', 225,           '2011-03-20 15:48:42.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:57:52.000', 193,           '2011-03-20 15:54:39.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 16:00:06.003', 0,             '2011-03-20 16:00:06.003'
UNION ALL SELECT 12345,         2,           '2011-03-20 16:00:06.010', 21616,         '2011-03-20 09:59:50.010'
more ▼

answered Mar 21, 2011 at 12:54 PM

KenJ gravatar image

KenJ
20.3k 1 4 12

Thanks for your reply. I was actually hoping to get something like 12345, 2011-03-20 09:00:00, 4 then 12345, 2011-03-20 09:30:00, 26 which shows that between 09:00 and 09:30 agent 12345 was ready for 4 minutes and between 09:30 and 10:00 the agent was ready for 26 minutes to take calls. I will edit my question to provide a fuller example table.
Mar 21, 2011 at 01:02 PM Ian Roke
(comments are locked)
10|1200 characters needed characters left

With luck, this will hopefully be closer to what you were expecting. The code is pretty sloppy, I'm afraid, so I'm open to suggestions on how to improve it. It should, however, work if the jobs start and end in the same day. If you have some overnight calls (start at 11 PM and finish at 1 AM, for example) or a call which lasts more than 24 hours, there would be some fidgeting with the comparisons and possibly the addition of another set of Time records for each additional day that things can run. Your examples didn't include this, though, so I coded lazily optimistically.

You can adjust the minute range (you said 30 minutes, but it sounded like that was a flexible requirement) and the begin and end times for display ranges, to keep from having too many records shown. Also, I left ReadyMinutes as an int; changing it to a decimal should be pretty easy if you do want the fractional minutes broken out as well.

declare @MinuteRange int;
set @MinuteRange = 30;
declare @BeginTime datetime;
set @BeginTime = '1900-01-01 07:00:00';
declare @EndTime datetime;
set @EndTime = '1900-01-01 18:00:00';


/* Setting up a time table.  sp_tally is a simple tally table, starting from 1. */ 
IF OBJECT_ID('tempdb..#Time') IS NOT NULL DROP TABLE #Time;

create table #Time
(
    T datetime
);

declare @sql nvarchar(max);
set @sql = N'insert into #Time select top ' 
    + cast(24 * 60 / @MinuteRange as nvarchar(4)) 
    + 'DATEADD(MINUTE, (N - 1) * @MinuteRange, ''1900-01-01 00:00:00'') from sp_tally;';
exec sp_executesql @sql, N'@MinuteRange int', @MinuteRange;

with records as
(
    select
       spans.SkillTargetID,
       spans.EventID,
       spans.EventStartDate,
       spans.T,
       (@MinuteRange * 60 - sum(EventEndTimeSecondsRemaining - EventStartTimeSecondsAlreadyElapsed)) / 60 as ReadyMinutes
    from
       (
         select 
          *,
          case
              when DATEDIFF(SECOND, t, EventStartTime) < 0 then 0
              else DATEDIFF(SECOND, t, EventStartTime)
          end as EventStartTimeSecondsAlreadyElapsed,
          case
              when DATEDIFF(SECOND, t, EventEndTime) > @MinuteRange * 60 then @MinuteRange * 60
              else DATEDIFF(SECOND, t, EventEndTime)
          end as EventEndTimeSecondsRemaining
         from 
          (
              select 
                 CAST(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT) 
                   - FLOOR(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventStartTime,
                 CAST(FLOOR(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventStartDate,
                 CAST(CAST(CAST(EventDateTime AS DATETIME) AS FLOAT) 
                   - FLOOR(CAST(CAST(EventDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventEndTime,
                 CAST(FLOOR(CAST(CAST(EventDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventEndDate,
                 SkillTargetID,
                 EventID,
                 EventDateTime,
                 EventDuration,
                 EventStartDateTime
              from
                 #AgentEventLog
          ) ael
          CROSS JOIN #Time t 
         where
          t.T between ael.EventStartTime and ael.EventEndTime
          OR (t.T <= EventStartTime AND t.T <= EventEndTime AND t.T >= DATEADD(MINUTE, -@MinuteRange, EventStartTime))
       ) spans
    group by
       spans.SkillTargetID,
       spans.EventID,
       spans.EventStartDate,
       spans.T   
),
RecordedEvents as
(
    select distinct 
       SkillTargetID, 
       EventID,
       CAST(FLOOR(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventStartDate
    from 
       #AgentEventLog
)
select
    re.SkillTargetID,
    re.EventID,
    cast(CAST(re.EventStartDate as float) as datetime) + t.T as EventInterval,
    coalesce(r.ReadyMinutes, @MinuteRange) as ReadyMinutes
from
    #Time t
    cross join RecordedEvents re
    left outer join records r 
       on r.T = t.T 
       and re.SkillTargetID = r.SkillTargetID 
       and re.EventID = r.EventID
       and re.EventStartDate = r.EventStartDate
where
    t.T between '1900-01-01 07:00:00' and '1900-01-01 18:00:00'
order by
    re.SkillTargetID,
    re.EventID,
t.T
more ▼

answered Mar 21, 2011 at 06:17 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Hi Kevin thanks for your solution. I am only intending to do this for one day. Agents don't always log out or log in correctly. so there will always be some limitations. Your solution works however you split it out by event. What I need is the total number of minutes that an agent is ready to take calls. This is when they are "not" not ready which is before the EventStartDateTime and after the EventDateTime of any EventID of 3. Does that make sense? If you look at my edit you will see some example output.
Mar 22, 2011 at 01:44 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x292

asked: Mar 21, 2011 at 11:06 AM

Seen: 1903 times

Last Updated: Mar 21, 2011 at 01:07 PM