x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 497

# Calculate Agent Shift Length

Hi All - Below is the body of a message I just posted in the Pentaho Community Forums.  I'm posting here too because I really need to come up with an approach to solve this problem.  The tools I've got at my disposal to solve this probelm are Pentaho Spoon, and or SQL (MySQL).

--------------------------------------------------------------------------------------------------------------
I've been chewing on this one for a few days and not sure how I should go about it. I've got some chat data, which i can't share with you, that has information like 'ChatAgent' 'StartTime', and 'EndTime'

My task is to calculate, with reasonable precision, how many hours an agent worked in a day. Obviously, I can't tap into any WFM system or alternate reports or I wouldn't be going down this road.

I can think of 2 approaches:

1) Shift Duration = Max(EndTime) - Min(StartTime), grouped by agent. This approach gets tricky if there is a lot of 'down time', where a person isn't chatting at the front or end of their shift. I actually don't think that this would be an issue because our agents stay pretty busy. That said, I'm running into issues where an agent's PREVIOUS shift from the day before crosses midnight. They end up looking like they've worked 20+ hours in some cases

2) Calculate duration (EndTime - Starttime) for each chat and sum those values, grouped by agent. This seems like the best approach but it gets complicated because of concurrency. An agent can be chatting with more than one customer at a time. If a person averaged a concurrency of 2 chats, an 8 hour shift would result in 16 hours of work using this method (I only want to know that they worked 8 hrs)

I found myself populating a database table with 24 hours of 10-second intervals, when I decided that there must be a better way :/

Any ideas how to do this within a spoon transformation? I'm also starting to wonder if I should attempt to write a UDF for MySQL. I'll admit I've got hardly any experience writing UDFs for SQL though.
0
ducky801
• 3
• 2
• 2
1 Solution

Commented:
> issues where an agent's PREVIOUS shift from the day before crosses midnight.
Does that mean that 'StartTime', and 'EndTime' do not include day information?
Can your data include more than one day of data?
If so, is there ordering information on the 'StartTime', and 'EndTime' data so that you can tell which 'StartTime' matches with which 'EndTime'?
And can we assume that durations will never exceed 24 hours?
Also, would we need to deal with Daylight Saving Time switches?
0

Commented:
Multiple solutions possible.
One way is to define shift boundaries and then calculate the hoursWorked at shift level rather than day level.
This is okay if you are OK tolerating 'long break's by agent.

To have more control on tolerating breaks, we need to compute the breaktime.
This can be done using updating-temp-variable-in-select-clause trick.

Solution will be something like this (assuming starttime and endtime have date component as well)
``````@maxAllowedBreak = 5
@maxEndtime = null;
@lastAgent = null;

select x.agent, min(starttime)-max(endtime) - interval sum(if(breaktime > @maxAllowedBreak, breaktime, null)) minute
from
(
select c.*,
timediff(c.starttime, coalesce(if(@lastAgent=c.agent, @maxEndtime, null),c.starttime), minutes) breaktime,
@maxEndtime=greatest(coalesce(if(@lastAgent=c.agent, @maxEndtime, null),c.starttime), c.endtime),
@lastAgent=c.agent
from chats c
where date(c.starttime) = :d
order by agent, starttime
) x
group by agent;
``````
Note: This not tested code. Most likely will have many syntax error. Treat this is pseudo-code.

There will be some inaccuracy due to double counting when an agent has chat that spans across day boundary and first chat of the next day starts before end of this day-boundary-crossing chat.
0

Author Commented:
c_kedar -

Thanks for posting this!

Can you walk me through what's happening with these vars?  I've got lots of experience with complex select queries, but have never really gotten into variables in SQL much.  I'm sure my life would be easier if i studied up a bit  ;)

This query almost seems like it's supposed to act procedurally. It seems like ordering by agent, starttime is VERY important here too, right?

Reading through this, it seems to me that lastAgent is being set the the value of agent of the previous row, then we're reading the last endtime (this is what makes the ordering important) of that agent if the current row is the same agent, otherwise we're using the current row's start time.  Is that what's happening?

Thanks again for the assistance
0

Author Commented:
Ozo -

-StartTime and EndTime are Timestamp DataTypes so they hold both Date and Time.

-The Data includes more than one day of data, years of it in fact

-There is one flat record per chat session.  Each record has a Start and End Time field

-Chat and shift duration shouldn't exceed 24 hours.  If they did, i'd just call it an 'outlier' and ignore the row

-I'm not concerned with DST

Thanks!
0

Commented:
-StartTime and EndTime are Timestamp DataTypes so they hold both Date and Time.
then why were there issues where an agent's PREVIOUS shift from the day before crosses midnight?
0

Author Commented:
Ozo -

Because  [Max(EndTime) - Min(StartTime)] would return a "really long" shift in that case, right?
0

Commented:
Yes, order by agent, startTime is very important.

Following simplified version of the same which makes the logic easy to understand:

``````@maxAllowedBreak = 5
@lastAgent = '';

select x.agent, min(starttime)-max(endtime) - interval sum(if(breaktime > @maxAllowedBreak, breaktime, null)) minute
from
(
select c.*,
case(
when @lastAgent<>c.agent then 0
else timediff(@maxEndTime, c.startTime, minutes)
) breaktime,
@maxEndtime=case(
when @lastAgent<>c.agent then c.endTime
else greatest(@maxEndtime, c.endTime)
),
@lastAgent=c.agent
from chats c
where date(c.starttime) = :d
order by agent, starttime
) x
group by agent;
``````

Hope this helps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.