Adding together intervals in AED?

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.

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:

• This probably can only be done well with a Stored Procedure. Being able to procedurally "step through" a list of data is incredibly useful for solving this.
• A lot of issues arise with trying to build this as a standard query using JOINs - the DateTime values will often be multiple seconds off from the actual interval boundaries, and this is difficult to account for as it's not consistent between subsequent rows.
• Any lines that belong to the same actual event will share the same ReasonCode, MRDomainID, SkillTargetID, LoginDateTime. However, this is not enough to differentiate different events, as any user can use the same reason code multiple times during a single login.

A Stored Procedure for this would likely follow this logic:

1. Pull a list of AED rows, sorted by LoginDateTime, SkillTargetID, MRDomainID, ReasonCode, then DateTime
2. Use a cursor to step through the list
3. Find the first row for an event (will be the earliest DateTime)
4. Check the next row - if the two rows' DateTime values are within Row 1's "Duration" of each other (+-10 seconds), consider them the same event.
5. Repeat (4) until you don't find another row for the event
6. Add up all durations for all rows for the same event
7. Repeat (3) until you've stepped through the whole list
8. Return a single row for each event (which would only differ from the event's first AED row in the "Duration" value)

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

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;

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;

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

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

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!

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

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