Solved

Calculate Agent Shift Length

Posted on 2014-02-10
7
476 Views
Last Modified: 2014-02-14
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
Comment
Question by:ducky801
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 39848858
> 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
 
LVL 2

Expert Comment

by:c_kedar
ID: 39855511
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;

Open in new window

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
 
LVL 5

Author Comment

by:ducky801
ID: 39856493
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 5

Author Comment

by:ducky801
ID: 39856506
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
 
LVL 84

Expert Comment

by:ozo
ID: 39857200
-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
 
LVL 5

Author Comment

by:ducky801
ID: 39857539
Ozo -

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

Accepted Solution

by:
c_kedar earned 500 total points
ID: 39858596
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;

Open in new window


Hope this helps.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question