How to show Start Time and End Time of Events from Agent_Event_Detail table in CUIC Report?

Sathiya Kumar · ‎08-21-2018

As you aware Agent_Event_Detail table of AWDB will be updating whenever Event change is occurring. When an agent going for Lunch @2PM and changing his state to Break @2:10Pm, then DB will update with Lunch Duration 600 Seconds. If an event is continuing beyond 30 minutes, this table will put an entry for every 30 minutes until the agent changing his current state.

In our custom report we need to show the Agent Event data from the Start of the Event to End of the event even though it got break at 30 minutes interval @ Agent_Event_Detail table.

In the CUIC report we are getting incorrect data as some time we are getting Duration more than 30 minutes but most of the times duration is breaking at 30 minutes.

Query Logic:

We are showing Event StartTime and EndTime from Agent_Event_Detail table by considering Row Number and Serial Number,

RowNum = ROW_NUMBER() OVER (PARTITION BY SkillTargetID ORDER BY DateTime, SkillTargetID),

SerialNum = ROW_NUMBER() OVER (PARTITION BY SkillTargetID, Event,Duration ORDER BY DateTime),

From Agent_Event_Detail.

After fetching the RowNum and SerialNum, we are selecting a field called Activity based on those RowNum and SerialNum values,

[Activity] = CASE  WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum=1) THEN CTE.DateTime

                                                                                                                                WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum=1) THEN CTE.DateTime

                                                                                                                                WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum<>1) THEN prev.DateTime

                                                                                                                                WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum<>1) THEN prev.DateTime

                                                                                                  ELSE CTE.DateTime

                                                                                  END

                                                                FROM CTE

                                                                LEFT JOIN CTE prev ON prev.RowNum = (CTE.RowNum-CTE.SerialNum)+1 AND prev.SkillTargetID=CTE.SkillTargetID

Start Time and End Time values are based on the above [Activity]. But it is showing incorrect data.

Full Query:

;WITH RCode ( ReasonCodeID, ReasonCode,ReasonText, Description )

AS (

  Select ReasonCodeID, ReasonCode,ReasonText, Description From Reason_Code

                Union All

                Select 9999,0,'Not Ready-Default', 'Not Ready-System Predefined'

                Union All

                Select 9999,-1,'Agent reinitialized (used if peripheral restarts)', 'Not Ready-System Predefined'

                Union All

                Select 9999,-2,'PG reset the agent, normally due to a PG failure', 'Not Ready-System Predefined'

                Union All

                Select 9999,-3,'An administrator modified the agent''s extension while the agent was logged in', 'Not Ready-System Predefined'

                Union All

                Select 9999,50002,'A CTI OS component failed, causing the agent to be logged out', 'Not Ready-System Predefined'

                Union All

                Select 9999,50003,'Agent was logged out because the Unified CM reported the device out of service', 'Not Ready-System Predefined'

                Union All

                Select 9999,50004,'Agent was logged out due to agent inactivity as configured in agent desk settings', 'Not Ready-System Predefined'

                Union All

                Select 9999,50005,'The Agent will be set to not ready with this code while talking on a call on the Non ACD line', 'Not Ready-System Predefined'

                Union All

                Select 9999,50020,'Agent was logged out when the agent''s skill group dynamically changed on the Administration & Data Server', 'Not Ready-System Predefined'

                Union All

                Select 9999,50040,'Mobile agent was logged out because the call failed', 'Not Ready-System Predefined'

                Union All

                Select 9999,50041,'Mobile agent state changed to Not Ready because the call fails when the mobile agent''s phone line rings busy.', 'Not Ready-System Predefined'

                Union All

                Select 9999,50042,'Mobile agent was logged out because the phone line disconnected while using nailed connection mode', 'Not Ready-System Predefined'

                Union All

                Select 9999,32767,'The agent''s state was changed to Not Ready because the agent did not answer a call and the call was redirected to a different agent or skill group', 'Not Ready-System Predefined'

),

CTE ( RowNum, SerialNum, DateTime, SkillTargetID, Event, Duration, ReasonCode)

AS (

  SELECT

                RowNum = ROW_NUMBER() OVER (PARTITIOn BY SkillTargetID ORDER BY DateTime, SkillTargetID),

    SerialNum = ROW_NUMBER() OVER (PARTITION BY SkillTargetID, Event,Duration ORDER BY DateTime),

    DateTime, SkillTargetID, Event, Duration, ReasonCode

  FROM

                                (Select DateTime,SkillTargetID, Event,

                                CASE WHEN (Duration=899 OR Duration=898 OR Duration=901 OR Duration=900) THEN 900

                                                 WHEN (Duration=1799 OR Duration=1798 OR Duration=1801 OR Duration=1800) THEN 1800

                                                 ELSE Duration end as 'Duration',

                                ReasonCode,RecoveryKey From Agent_Event_Detail Where

                                SkillTargetID IN (5259)                  

                                And (Convert(varchar(10),DateTime,110)>= '07-10-2018' and convert(varchar(10),DateTime,110) <= '07-10-2018') ) A
)
--select * from CTE
,
CTE2 AS (
                                Select [Activity], Convert(varchar(10), [Activity], 101) AS [Date],
Stuff(right(convert(varchar(30), [Activity], 109), 14), 9, 4, ' ') AS [End Time],
                                                                SkillTargetID, [Agent Name], Event,  [Duration], Z.ReasonCode [Reason Code], R.ReasonText [Reason], PrevDateTime,CTEDateTime, RowNum,SerialNum,PrevRow,CTENewRow From 
                                (Select CTE.RowNum RowNum, CTE.SerialNum SerialNum, CTE.DateTime CTEDateTime, CTE.SkillTargetID,
                                                (Select B.LastName +', '+B.FirstName From Agent (nolock) Join Person B On Agent.PersonID = B.PersonID Where SkillTargetID=CTE.SkillTargetID) [Agent Name], prev.DateTime PrevDateTime, prev.RowNum PrevRow,((CTE.RowNum-CTE.SerialNum)+1) CTENewRow,
                                                Event = CASE WHEN CTE.Event = 1 THEN 'Sign-on' WHEN CTE.Event=2 THEN 'Sign-off'  WHEN CTE.Event=3 THEN 'Not-Ready' Else 'Unknown' END,
                                CTE.Duration, CTE.ReasonCode,
                                                [Activity] = CASE               WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum=1) THEN CTE.DateTime
                                       WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum=1) THEN CTE.DateTime                                       WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum<>1) THEN prev.DateTime
                                       WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum<>1) THEN prev.DateTime
         ELSE CTE.DateTime
                                                                                  END
                                                                FROM CTE
                                                                LEFT JOIN CTE prev ON prev.RowNum = (CTE.RowNum-CTE.SerialNum)+1 AND prev.SkillTargetID=CTE.SkillTargetID
                                )
                                Z LEFT JOIN RCode R ON R.ReasonCode = Z.ReasonCode 
)
--Select * From CTE2 where [Reason Code]=107 ORDER BY  Activity, [Agent Name]
Select Activity, Date, Stuff(right(convert(varchar(30), DATEADD(second, -(Sum(Duration)),[Activity]), 109), 14), 9, 4, ' ') AS [Start Time],
[End Time], SkillTargetID, [Agent Name], Event, SUM(Duration) AS [Duration], [Reason Code], Reason From CTE2
Where [Reason Code]=107
GROUP BY [Activity], Date, [End Time], SkillTargetID, [Agent Name], Event, [Reason Code], Reason
Order By [Agent Name], Activity

Reason for this issue:

If SerialNum is based on SkillTargetID, Event, Duration it is showing different values for same entry and showing 1 for different entries as expected and due to that if we have below scenario we are getting incorrect Start Time and End Time and in the report.

Agent Name – Date Time – ReasonText – Duration

Agent 1 – 2018-08-20 10:00:00 – BioBreak – 150 Seconds

Agent 1 – 2018-08-20 10:30:00 – BioBreak – 1800 Seconds

Agent 1 - 2018-08-20 11:00:00 – BioBreak – 1800 Seconds

Agent 1 – 2018-08-20 11:30:00 – BioBreak – 1800 Seconds

Agent 1 – 2018-08-20 11:42:00 – BioBreak – 720 Seconds

Here SerialNum value will show 1 for First Record as well as last record due to this query is thinking they are different events and not the same but actually all are same continues event that should show as in single row in report.

Thanks and Regards,
Sathiya Kumar V M
Senior Database Developer

ctwoods · ‎09-29-2018

Hi, I originally thought the DateTime field was the time the event was initiated, but after reading your post, I realized it is the time when the state changes to something else, and the duration is the cumulative time accrued for the state before it transitioned to a different state. So, EndTme (for the interval) is the DateTime: Below is the query I use. This should give you what you need. I select my target agents based on the Team they belong to, you can modify the Agents CTE to fit your organization:

SET
ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate= '2018-07-31 00:00:00'
SET @EndDate= '2018-08-01 00:00:00'
SET DATEFIRST 1;
WITH
Agents ([AgentID],Team)
AS
(
SELECT Distinct
Ag.[SkillTargetID],AgT.EnterpriseName
FROM [dbo].[Agent_Team] AgT
JOIN [dbo].[Agent_Team_Member] AgTM
ON AgT.AgentTeamID=AgTM.AgentTeamID
JOIN [dbo].[Agent] Ag
ON Ag.SkillTargetID=AgTM.SkillTargetID
JOIN (SELECT DISTINCT SkillTargetID FROM dbo.Agent_Interval WHERE [DateTime] >=@StartDate AND [DateTime] <@EndDate) T ON Ag.SkillTargetID=T.SkillTargetID
WHERE
AgT.[EnterpriseName] LIKE '%TT%'
OR AgT.[EnterpriseName] LIKE '%ACI%'
OR AgT.[EnterpriseName] LIKE '%BTC%'
OR AgT.[EnterpriseName] LIKE '%MoB%'
),

AgentEventDetail(Dt,DateTime,Team,SkillTargetID,ReasonCode, Duration)
AS(Select Dt,DateTime,Team,SkillTargetID,ReasonCode,SumVal
From(
SELECT *,
SUM(Duration) Over(Partition by Dt,SkillTargetID,Diff,ReasonCode Order by Dt) as SumVal
,ROW_NUMBER() Over(Partition by Dt,SkillTargetID,Diff,ReasonCode Order by Dt) as RowCnt2
FROM (
Select cast(DateTime as Date) as Dt,DateTime,Team,SkillTargetID, Duration, ReasonCode, RowCnt_-Row_ as Diff
FROM
(SELECT [DateTime],Team,[SkillTargetID],[Event],[Duration],[ReasonCode]
,ROW_NUMBER() Over(Partition by cast([DateTime] as date),SkillTargetID Order by DateTime) as RowCnt_
,ROW_NUMBER() Over(Partition by cast([DateTime] as date),SkillTargetID,ReasonCode Order by DateTime) as Row_
FROM [ucce_awdb].[dbo].[Agent_Event_Detail] A with (nolock)
JOIN Agents ON Agents.AgentID=A.SkillTargetID
WHERE [DateTime] >=@StartDate AND [DateTime] <@EndDate and NOT [ReasonCode] =50003 and NOT [ReasonCode] =0
)T
)T2
)T3
Where RowCnt2=1
)

SELECT Dt=Cast([DateTime] as Date)
,Site= Case When Left(Team,3) Like 'T%' THEN 'TRN' ELSE Upper(Left(Team,3)) end
,Team
,B.Extension
,D.FirstName
,D.LastName
,BeginTime =dateadd(ss,-[Duration],cast([DateTime] as Time(0)))
,EndTime=cast([DateTime] as time(0))

--,BeginTime=cast([DateTime] as time(0))
-- ,EndTime =dateadd(ss,[Duration],cast([DateTime] as Time(0)))
,[Duration]
,A.[ReasonCode]
,ReasonText =
CASE
WHEN A.[ReasonCode] =50001 THEN 'The CTI OS client disconnected.'
WHEN A.[ReasonCode] =50002 THEN 'A CTI OS component failed.'
WHEN A.[ReasonCode] =50003 THEN 'Unified CM reported the device out of service.'
WHEN A.[ReasonCode] =50004 THEN 'Agent inactivity as configured in agent desk settings.'
WHEN A.[ReasonCode] =50005 THEN 'Agent talking on a call on Non-ACD line.'
WHEN A.[ReasonCode] =50010 THEN 'Multiple consecutive calls not answered--Agent set to Not Ready.'
WHEN A.[ReasonCode] =50020 THEN 'Agents skill group dynamically changed on the Administration & Data Server.'
WHEN A.[ReasonCode] =50030 THEN 'If an agent is logged in to a dynamic device target that is using the same dialed number (DN) as the PG static device target, the agent is logged out.'
WHEN A.[ReasonCode] =50040 THEN 'Mobile agent was logged out because the call failed.'
WHEN A.[ReasonCode] =50041 THEN 'Mobile agent phone line rings busy.'
WHEN A.[ReasonCode] =50042 THEN 'Mobile agent phone line disconnected while using nailed connection mode.'
WHEN A.[ReasonCode] =50041 THEN 'Agent phone line rings busy--Agent was logged out.'
WHEN A.[ReasonCode] =32767 THEN 'Agent did not answer, call redirected.'
WHEN A.[ReasonCode] =32762 THEN 'Agent Off-Hook, Outbound Call.'
ELSE
Case
When len(R.ReasonText)>4 and Charindex('_',R.ReasonText)=4 Then right(R.ReasonText,len(R.ReasonText)-4)
When R.ReasonText is null then 'Undefined'
ELSE R.ReasonText END
END
FROM AgentEventDetail A with (nolock)
LEFT JOIN (Select Distinct SkillTargetID, Extension FROM Agent_Interval WHERE [DateTime] >=@StartDate AND [DateTime] <@EndDate) B ON A.SkillTargetID =B.SkillTargetID
LEFT JOIN [dbo].[Agent] C with (nolock) ON A.SkillTargetID =C.SkillTargetID
LEFT JOIN dbo.Person D with (nolock) ON C.PersonID=D.PersonID
LEFT JOIN [dbo].[Reason_Code] R with (nolock) ON A.ReasonCode=R.ReasonCode