Visualizing Tasks Done per Work Hour

user00344

Hey Dojo!

I am having a hard time creating a dataset to allow me to visualize tasks done per work hour by an employee.  The problem is I have two datasets to start with (tasks and daily schedule) which only have a single commonality being date.

 

The tasks dataset creates a new row for each task done in our system which could potentially be 100+ tasks a day per person.  The schedule dataset lists each employee each day on a row with their start time/end time and total hours worked.

 

What would you suggest for bringing these two together to visualize?

AS

If you want to view hourly production by employee, you need to get the employees assigned to the tasks.  In order to do that, you have to know who works at what time and what time tasks were completed.

 

In the absence of a common thread like employee_id, does the tasks dataset have a task completion date or other type of date related to the task?

In the schedule dataset, do multiple employees work simultaneously? Or only one at a time? If multiples, how would you know which of the several did the task?

 

In the simplest of circumstances, your data has task completion times and only one employee to possibly assign tasks to.  In SQL, that would look something like:

 

select

t.task_id

,t.task_completion_datetime

,e.employee

from

tasks t

join employee_schedule e

on t.task_completion_datetime >= e.shift_start_datetime

and t.task_completion_datetime <= e.shift_end_datetime

user00344

Thank you this did help, but I still need some more advice to get my end result.

 

I know have the two tables joined together as you describe below.  This leaves me with these key columns among others.

 

Employee, Action, Timestamp of Action completed, Emply Daily Start Time, Employ Daily End Time, Hours (worked that day)

 

The issue I have is because someone can do 100+ actions in a day, the sum(hours) may end up being 800 for a particular day.  How would you suggest I break up the hours to that I can keep that total hours worked consistent if filtering by specific days, but also by specific hours (ie. anything in the 2pm hour block)?

user00344

To make this a little easier to understand.  My table ends up looking like what is listed below and I want to be able to count(action) / sum(Hours).  The hours field needs to be restructured though so that it doesn't count the same hours multiple times.

 

Date  |  Action  |  Hours |

6/21   |   Act 1   |    8      |

6/21   | Act 2     |    8      |

6/21   | Act 3     |    8      |

6/22   | Act 4     |     9     |

6/22   |  Act 5    |     9     |

AS

In the data, does each row describe a task completed (task id, task start time, task end time, employee who completed the task, etc)?  Or is each row a summary of daily performance by employee (employee name, shift start time, shift end time, tasks created, hours worked, etc)?

 

Mixing daily aggregations (employee hours worked) with line item information (task id) isn't going to work well.  Do you have task start time available in addition to task end time?  If you have that, query the task hours worked instead of employee daily hours worked.  Then when you sum up the hours worked, and tasks aren't worked on simultaneously, you're summing up hours worked on tasks.

 

You'll have to be careful about how you sum up hours worked per clock hour, though, since tasks can potentially roll from one hour to the next. If a task ends at 12:01PM, but was worked on starting at 11:01, 59 minutes were worked at the 11oclock hour and 1 minute at 12. If you just pull the clock hour from the task end time column, you'll have 60 minutes falling in the 12 oclock hour.  That's might not be what you want.