Solved

Calculate Agent Shift Length

Posted on 2014-02-10
7
440 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This question got me thinking... (http://www.experts-exchange.com/questions/28707487/GLOBALS.html) Why shouldn't we use Globals? This is a simple question without a simple answer.  How do you explain these concepts to a programmer w…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now