Turnover Statistics – Part 2

A couple of weeks back on Tutorial Tuesday we talked about turnover statistics in a hospital setting. We used a simple spreadsheet to show how many new care providers a new patient met within the time that they were there.

This week we are going to take a deeper look at turnover statistics with a different example.

Our Example

The company that we are examining this week is an I.T. Consulting Company that provides Technical Assistance, Hardware Setup, and Maintenance. The same person might be doing all three tasks for different units of a large company that the I.T. Consultants are providing service for.

The problem to solve:

We know when the person starts and stops working on each particular task, and for each specific unit. What we want to know easily is the first date that the consultant began working with the unit, and the last time that the person worked with the unit.

Using SQL

Our problem can be solved very efficiently using SQL. First, we look at the data that we have:

Download sample data from here.

Then we import data into our database. I’m not going to describe this here. If you are using a MySQL database, you can use this tutorial to import your data.

Once we have the data in our database, we can run a simple query to select and group the data. The query we will run is:

SELECT Unit, Name, min(Start), max(Finish) FROM Work By Location 
 group by Unit, name

Let me explain:

First, we SELECT data. We choose to select 4 fields:

  1. The unit where the person did the work
  2. The person’s name
  3. The minimum (smallest) start date for the person at the unit. It doesn’t matter what work the person did, because we only want to know when they started with the unit.
  4. The maximum (last) date that the person worked for the unit. Again what work they did doesn’t matter.

Once we have the data we need, we choose to GROUP the data. We want the output to give us a group by Unit, and by the Name of the person. We don’t want to know the type of work because we have that data in our original table.

The output from this query is:

If we had wanted to know only the time that anyone started at a specific unit, we could run the same query, but only ask the data to be grouped by Unit. The output would have told us the first time the consultants worked with a specific unit, and the last time.

This is a simple example that helps us get started understanding when the units first encountered the consultants.