SELECT
(Person.FirstName + ' ' + Person.LastName) as FullName,
CASE AST.AgentState
WHEN 0 THEN 'Logged Off'
WHEN 1 THEN 'Logged On'
WHEN 2 THEN 'Not Ready'
WHEN 3 THEN 'Ready'
WHEN 4 THEN 'Talking'
WHEN 5 THEN 'Work Not Ready'
WHEN 6 THEN 'Work Ready'
WHEN 7 THEN 'Busy Other'
WHEN 8 THEN 'Reserved'
WHEN 9 THEN 'Call Initiated'
WHEN 10 THEN 'Call Held'
WHEN 11 THEN 'Active'
WHEN 12 THEN 'Paused'
WHEN 13 THEN 'Interrupted'
WHEN 14 THEN 'Not Active'
ELSE 'Unknown'
END AS AgentState,
CASE
WHEN AST.AgentState = 0 THEN 0
WHEN AST.SkillTargetID = AST.SkillTargetID THEN AST.TimeDefference
END as TimeDefference,
AST.ReasonCode,
AST.SkillTargetID,
CASE RC.ReasonText WHEN 'Undefined' THEN '' ELSE RC.ReasonText END ReasonText,
AST.Date_Time
FROM
(
Select
T11.Date_Time as Date_Time,
T11.AgentState as AgentState ,
T11.SkillTargetID as SkillTargetID,
T11.ReasonCode as ReasonCode,
DATEDIFF(ss,T11.T1_TIME_STAMP,T22.T2_TIME_STAMP) as TimeDefference
from
(
Select
T1.Date_Time as Date_Time,
T1.T1_TIME_STAMP as T1_TIME_STAMP ,
T1.ReasonCode as ReasonCode,
T1.AgentState as AgentState,
T1.SkillTargetID as SkillTargetID,
ROW_NUMBER() OVER (ORDER BY SkillTargetID,T1.T1_TIME_STAMP ) AS RN
From
(
SELECT
ReasonCode,
AgentState,
SkillTargetID,
CONVERT(char(8), dateadd(millisecond, -datepart(millisecond, DateTime), DateTime), 108) AS T1_TIME_STAMP ,
dateadd(millisecond, -datepart(millisecond, DateTime), DateTime) as Date_Time
FROM Agent_State_Trace Where DateTime >= :startDate and DateTime <= :endDate
AND SkillTargetID in (:agent_list)
Group by
ReasonCode,
AgentState ,
SkillTargetID,
CONVERT(char(8), dateadd(millisecond, -datepart(millisecond, DateTime), DateTime), 108),
dateadd(millisecond, -datepart(millisecond, DateTime), DateTime)
) T1
)T11
LEFT JOIN
(
Select
T2.Date_Time as Date_Time,
T2.T2_TIME_STAMP as T2_TIME_STAMP,
T2.ReasonCode as ReasonCode,
T2.AgentState as AgentState,
T2.SkillTargetID as SkillTargetID,
ROW_NUMBER() OVER (ORDER BY SkillTargetID,T2.T2_TIME_STAMP ) AS RN
From
(
SELECT
ReasonCode,
AgentState ,
SkillTargetID,
CONVERT(char(8), dateadd(millisecond, -datepart(millisecond, DateTime), DateTime), 108) AS T2_TIME_STAMP ,
dateadd(millisecond, -datepart(millisecond, DateTime), DateTime) as Date_Time
FROM Agent_State_Trace Where DateTime >= :startDate and DateTime <= :endDate
AND SkillTargetID in (:agent_list)
Group by
ReasonCode,
AgentState ,
SkillTargetID,
CONVERT(char(8), dateadd(millisecond, -datepart(millisecond, DateTime), DateTime), 108),
dateadd(millisecond, -datepart(millisecond, DateTime), DateTime)
) T2
)T22
ON T11.RN= T22.RN-1
AND T11.SkillTargetID = T22.SkillTargetID
Group by T11.Date_Time, T11.AgentState, T11.SkillTargetID, T22.SkillTargetID, T11.ReasonCode, T11.AgentState, T11.T1_TIME_STAMP, T22.T2_TIME_STAMP
) AST
LEFT OUTER JOIN
(SELECT
ReasonCode,
ReasonText
FROM Reason_Code
GROUP BY ReasonCode, ReasonText) RC
ON RC.ReasonCode=AST.ReasonCode
LEFT JOIN Agent
ON Agent.SkillTargetID=AST.SkillTargetID
LEFT JOIN Person
ON Person.PersonID=Agent.PersonID
LEFT JOIN Agent_Interval
ON Agent_Interval.SkillTargetID = AST.SkillTargetID
GROUP BY
AST.SkillTargetID, Person.FirstName, Person.LastName, AST.AgentState, AST.ReasonCode, RC.ReasonText, AST.TimeDefference, AST.Date_Time
ORDER BY
AST.Date_Time,
AST.SkillTargetID
