04-08-2015 01:30 AM
Hi,
In Agent_Event_Detail, if an agent is on the same event for more than 15 minutes, the report below shows the same state in multiple rows.
Is it possible to just get the first timestamp and the full duration of the next ones which is tied to she very same event?
Here's the query:
SELECT
(Person.FirstName + ' ' + Person.LastName) as FullName,
EventStart = DATEADD(ss,-Duration,DateTime),
EventEnd = DateTime,
aed.Duration,
Reason = rc.ReasonText + '(' + CONVERT(varchar(10),rc.ReasonCode) + ')',
aed.ReasonCode
FROM Agent_Event_Detail aed
LEFT OUTER JOIN Reason_Code rc on aed.ReasonCode = rc.ReasonCode
JOIN Agent ON Agent.SkillTargetID=aed.SkillTargetID
JOIN Person ON Person.PersonID=Agent.PersonID
WHERE aed.SkillTargetID = :AgentSkillTargetID
and aed.DateTime >= :startDate
and aed.DateTime < :endDate
and aed.ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
order by DateTime,Reason
And here's the output, I've marked in red on what I hope can be merged.
Solved! Go to Solution.
04-10-2015 07:40 AM
Robert,
Your initial "a" query should be ordered by SkillTargetID then DateTime. Also, your "b" query should only join rows with the same SkillTargetID:
;WITH a AS
(
SELECT
ReasonCode,
DateTime,
SkillTargetID,
ROW_NUMBER() OVER(ORDER BY DateTime) AS RN
FROM Agent_State_Trace rt
Where DateTime >= :startDate
and DateTime <= :endDate
and SkillTargetID IN (:AgentSkillTargetID)
ORDER BY SkillTargetID, DateTime
),
b AS
(
SELECT
a1.ReasonCode,
a1.SkillTargetID,
a1.DateTime,
ROW_NUMBER() OVER(ORDER BY a1.DateTime) AS RN
FROM a a1
LEFT OUTER JOIN a a2
ON a2.RN = a1.RN - 1 AND a1.SkillTargetID=a2.SkillTargetID
WHERE
(a1.ReasonCode != a2.ReasonCode) OR
(a2.RN IS NULL)
)
SELECT
(Person.FirstName + ' ' + Person.LastName) as FullName,
b1.ReasonCode,
RC.ReasonText,
b1.DateTime AS StartDate,
b2.DateTime AS EndDate,
DATEDIFF(s, b1.DateTime, b2.DateTime) + 1 AS Duration
FROM b b1
LEFT OUTER JOIN (SELECT
ReasonCode,
ReasonText
FROM Reason_Code
GROUP BY ReasonCode, ReasonText
) RC
ON RC.ReasonCode=b1.ReasonCode
LEFT OUTER JOIN (SELECT
ReasonCode,
DateTime,
SkillTargetID
FROM Agent_State_Trace
WHERE ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
) AST
ON AST.DateTime=b1.DateTime AND AST.SkillTargetID = b1.SkillTargetID
LEFT JOIN Agent
ON Agent.SkillTargetID=b1.SkillTargetID
LEFT JOIN Person
ON Person.PersonID=Agent.PersonID
LEFT OUTER JOIN b b2 ON b2.RN = b1.RN + 1
WHERE AST.ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
ORDER BY b1.DateTime;
-Jameson
04-09-2015 07:40 AM
Robert,
I had put a lot of thought into this same problem months ago, and never got to a good working report with it. I did take notes on it, though... so here's some of my thoughts on it:
A Stored Procedure for this would likely follow this logic:
While I think I could manage to write the above procedure, I simply haven't had the time or enough need for the report to get it done.
-Jameson
04-10-2015 02:36 AM
Hi Jameson,
Many thanks for the reply!
However, I think stored procedures are way over my head at the moment.
I know you're not a fan of Agent State Trace, but I managed to create a script by modifying something I found on Google.
It looks for the next row and calculates the duration.
Here's the output:
The only thing I would like to change now is to filter out some states (so it only shows the marked above)
I added this:
and ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
But then it messes up the duration and the EndDate because its way longer until the next row.
Do you have any idea on how I can hide the rows I don't want - but the query still look for it it?
Thanks!
Here's the code:
;WITH a AS
(
SELECT
ReasonCode,
DateTime,
SkillTargetID,
ROW_NUMBER() OVER(ORDER BY DateTime) AS RN
FROM Agent_State_Trace rt
Where DateTime >= :startDate
and DateTime <= :endDate
and SkillTargetID IN (:AgentSkillTargetID)
-- and ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
),
b AS
(
SELECT
a1.ReasonCode,
a1.SkillTargetID,
a1.DateTime,
ROW_NUMBER() OVER(ORDER BY a1.DateTime) AS RN
FROM a a1
LEFT OUTER JOIN a a2 ON a2.RN = a1.RN - 1
WHERE
(a1.ReasonCode != a2.ReasonCode) OR
(a2.RN IS NULL)
)
SELECT
(Person.FirstName + ' ' + Person.LastName) as FullName,
b1.ReasonCode,
CASE RC.ReasonText
WHEN 'Undefined' THEN ''
ELSE RC.ReasonText
END AS ReasonText,
b1.DateTime AS StartDate,
b2.DateTime AS EndDate,
DATEDIFF(s, b1.DateTime, b2.DateTime) + 1 AS Duration /* Fixme? */
FROM b b1
LEFT OUTER JOIN (SELECT
ReasonCode,
ReasonText
FROM Reason_Code
GROUP BY ReasonCode, ReasonText
) RC
ON RC.ReasonCode=b1.ReasonCode
LEFT JOIN Agent
ON Agent.SkillTargetID=b1.SkillTargetID
LEFT JOIN Person
ON Person.PersonID=Agent.PersonID
LEFT OUTER JOIN b b2 ON b2.RN = b1.RN + 1
ORDER BY b1.DateTime;
04-10-2015 04:27 AM
Solved it - can probably be improved - but it works!
I made an outer join on AST and added pointed the WHERE clause to this.
Sharing the query in case someone wants it (probably not but still.. )
;WITH a AS
(
SELECT
ReasonCode,
DateTime,
SkillTargetID,
ROW_NUMBER() OVER(ORDER BY DateTime) AS RN
FROM Agent_State_Trace rt
Where DateTime >= :startDate
and DateTime <= :endDate
and SkillTargetID IN (:AgentSkillTargetID)
),
b AS
(
SELECT
a1.ReasonCode,
a1.SkillTargetID,
a1.DateTime,
ROW_NUMBER() OVER(ORDER BY a1.DateTime) AS RN
FROM a a1
LEFT OUTER JOIN a a2 ON a2.RN = a1.RN - 1
WHERE
(a1.ReasonCode != a2.ReasonCode) OR
(a2.RN IS NULL)
)
SELECT
(Person.FirstName + ' ' + Person.LastName) as FullName,
b1.ReasonCode,
RC.ReasonText,
b1.DateTime AS StartDate,
b2.DateTime AS EndDate,
DATEDIFF(s, b1.DateTime, b2.DateTime) + 1 AS Duration
FROM b b1
LEFT OUTER JOIN (SELECT
ReasonCode,
ReasonText
FROM Reason_Code
GROUP BY ReasonCode, ReasonText
) RC
ON RC.ReasonCode=b1.ReasonCode
LEFT OUTER JOIN (SELECT
ReasonCode,
DateTime,
SkillTargetID
FROM Agent_State_Trace
WHERE ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
) AST
ON AST.DateTime=b1.DateTime AND AST.SkillTargetID = b1.SkillTargetID
LEFT JOIN Agent
ON Agent.SkillTargetID=b1.SkillTargetID
LEFT JOIN Person
ON Person.PersonID=Agent.PersonID
LEFT OUTER JOIN b b2 ON b2.RN = b1.RN + 1
WHERE AST.ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
ORDER BY b1.DateTime;
04-10-2015 05:26 AM
Ok, I'm stuck again...
When running this report on 1 agent the results are fine.
However, when running it on more than 1 agents, the EndDate and Duration gets messed up because there are multiple agents in between eachother because its sorting by datetime.
Any idea on how to solve this?
Result, one agent:
Result on the same agent when selecting multiple agents
04-10-2015 07:40 AM
Robert,
Your initial "a" query should be ordered by SkillTargetID then DateTime. Also, your "b" query should only join rows with the same SkillTargetID:
;WITH a AS
(
SELECT
ReasonCode,
DateTime,
SkillTargetID,
ROW_NUMBER() OVER(ORDER BY DateTime) AS RN
FROM Agent_State_Trace rt
Where DateTime >= :startDate
and DateTime <= :endDate
and SkillTargetID IN (:AgentSkillTargetID)
ORDER BY SkillTargetID, DateTime
),
b AS
(
SELECT
a1.ReasonCode,
a1.SkillTargetID,
a1.DateTime,
ROW_NUMBER() OVER(ORDER BY a1.DateTime) AS RN
FROM a a1
LEFT OUTER JOIN a a2
ON a2.RN = a1.RN - 1 AND a1.SkillTargetID=a2.SkillTargetID
WHERE
(a1.ReasonCode != a2.ReasonCode) OR
(a2.RN IS NULL)
)
SELECT
(Person.FirstName + ' ' + Person.LastName) as FullName,
b1.ReasonCode,
RC.ReasonText,
b1.DateTime AS StartDate,
b2.DateTime AS EndDate,
DATEDIFF(s, b1.DateTime, b2.DateTime) + 1 AS Duration
FROM b b1
LEFT OUTER JOIN (SELECT
ReasonCode,
ReasonText
FROM Reason_Code
GROUP BY ReasonCode, ReasonText
) RC
ON RC.ReasonCode=b1.ReasonCode
LEFT OUTER JOIN (SELECT
ReasonCode,
DateTime,
SkillTargetID
FROM Agent_State_Trace
WHERE ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
) AST
ON AST.DateTime=b1.DateTime AND AST.SkillTargetID = b1.SkillTargetID
LEFT JOIN Agent
ON Agent.SkillTargetID=b1.SkillTargetID
LEFT JOIN Person
ON Person.PersonID=Agent.PersonID
LEFT OUTER JOIN b b2 ON b2.RN = b1.RN + 1
WHERE AST.ReasonCode IN ('07','107','126','49','63','77','91','08','108','127','50','64','78','92')
ORDER BY b1.DateTime;
-Jameson
04-20-2015 06:15 AM
Jameson,
Many thanks for this!
However, it seems like I can't order anything in the "a" query. It gives me the following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
So I changed the ROW_NUMBER code to:
ROW_NUMBER() OVER(ORDER BY SkillTargetID, DateTime) AS RN
But it seems like the End time returns some strange number if there isnt any end time on the state (if they currently are on this state).
Any ideas on how I can do to prevent this?
Thanks!
04-20-2015 07:24 AM
You don't need to worry about ORDERing in the first ("a", or second "b") query. All it's doing is building a virtual table, there is no value to the order of things at that stage. Where you want the ORDER is in the final query where everything gets put together.
Regards,
Jack Parker
04-20-2015 07:36 AM
Robert,
Try replacing these:
b2.DateTime AS EndDate,
DATEDIFF(s, b1.DateTime, b2.DateTime) + 1 AS Duration
With this:
ISNULL(b2.DateTime,:endDate) AS EndDate,
DATEDIFF(s, b1.DateTime, ISNULL(b2.DateTime,:endDate)) + 1 AS Duration
-Jameson
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide