Get agent events for a single agent

(fnc_agent_events_window)

Please see more information, explanations and examples in ‘Audit log’ – see all events for an agent

Object name: fnc_agent_events_window
Type: Table value function
Description:

Delivers the so called agent events for a particular agent, and for a specified period window.

The main source is the table agent_events, but in addition to what’s found there some new events will be created (available and wrap up events). Also the start time, the end time, and the duration, of those events which are ongoing at the start and/or end of the specified period window will be adjusted to “fit” into these time frame “borders”.

It is also possible (but not required) to specify an additional time frame (not date, but ’HH:MM’) for which the selected events should be further narrowed. For example you may ask for all events starting on January 1st 2013, and ending January 31st 2013, but limited only to those events appearing between ‘09:00’ and ’11:30’. As described above, also for this additional time frame all ongoing events at the “border” will be adjusted.
Parameters:
Name Datatype Description
@agent_id int The (unique) id for the agent to search for.
@dte_from datetime Date/time for the start of the period.
@dte_to datetime Date/time for the start of the period.
@mode int
  • A “flag” used to decide the outcome of some of the new (or old) events:
  • 0 = Wrap up events (type ‘w’) will be created and returned (in addition to the ‘normal’ events).
  • 1 = Wrap up (type ‘w’) and available events (type’a’) will be created (added). ‘r’-events (return from pause) will be removed (even if they exist in table agent_events) – because ‘r’ is implicit expressed through the ‘a’ events.
  • 2 = As 1, but the ‘r’-events are also returned.
  • We believe that the value of 1 is the one that will be used in most cases.
@time_from char(5)
  • Optional (though, must be in parameter list, but can be NULL or empty string). If set, the @time_from and @time_to specifies a further (more “narrow”) time limit (HH:MM) within each day the events should belong to. In most cases, these parameters is only relevant to specify in the @dte_from and @dte_to spans over more than one day.
  • Some rules:
  • Valid format is HH:MM, all five digits must always be given.
@time_to should be greater than @time_from
@time_to char(5) See description for @time_from above.
Returns:

Returns a table with a subset of the columns from the table agent_events, and with two additional columns for the adjusted start and duration values (see Description above.

The table returned is like this (columns without description is described under description for table agent_events)

Column name Datatype Description
agent_id int The (unique) id for the agent to search for
dte_start datetime The (original) start for the event.
adj_dte_start datetime The start for the event within the period window. Will differ from dte_start for those events being ongoing at the time specified by parameter @dte_from / @time_from.
service_num varchar(100)  
event_type varchar(10)
result_code varchar(10)
duration_sec int The original duration of the event.
adj_duration_sec int The adjusted duration of the event. Will differ from duration_sec for those events being ongoing at the time specified by parameter @dte_to / @time_to.
duration_speak_sec int  
queue_key nvarchar(100)
pause_type_name nvarchar(100)
pause_type_id int
internal_odr_id int
Samples:
  • Get the events for agent 9999 between 08:30 and 16:00 on May 2nd 2013: 
    select * 
from [dbo].[fnc_agent_events_window](
		9999,
			'2-may-2016 08:30',
 			'2-may-2016 16:00',
			 1, NULL, NULL)
order by dte_start
  • Get the events for agent 9999 between 08:30 and 16:00 on May 2nd 2016, but exclude Login events (since time logged in are ‘included’ in Available and other events) 
    select * 
from [dbo].[fnc_agent_events_window](
		9999,
			'2-may-2016 08:30',
 			'2-may-2016 16:00',
			 1, NULL, NULL)
where event_type not in ('i')
order by dte_start
  • For agent 9999, find the total sum per event (and per pause type) between 08:30 and 16:00 on May 2nd 2016: 
    select  event_type, pause_type_name, count(*) Ant,
sum(adj_duration_sec) AS sumAdjDurSec 
from [dbo].[fnc_agent_events_window](
		9999,'2-may-2016 08:30:00', '2-may-2016 16:00', 
		1, NULL, NULL)
group by event_type, pause_type_name

     
Back to Top

Published

Last updated

3
-1

Recent documents

Popular documents