?
Solved

Average Tenure Time

Posted on 2013-12-10
7
Medium Priority
?
343 Views
Last Modified: 2013-12-10
I ran a query from MySQL to pull each employee and their start date.

My task is to calculate the average tenure date for the company.

I have a function that tells me how long each employee has been here (i.e, 2 years, 3 months, 4 days), but I need to get the average of that, and that is where I am stuck at...

function employeeLength($time1, $time2, $precision = 3) {
    // If not numeric then convert texts to unix timestamps
    if (!is_int($time1)) {
      $time1 = strtotime($time1);
    }
    if (!is_int($time2)) {
      $time2 = strtotime($time2);
    }
 
    // If time1 is bigger than time2
    // Then swap time1 and time2
    if ($time1 > $time2) {
      $ttime = $time1;
      $time1 = $time2;
      $time2 = $ttime;
    }
 
    // Set up intervals and diffs arrays
    $intervals = array('year','month','day','hour','minute','second');
    $diffs = array();
 
    // Loop thru all intervals
    foreach ($intervals as $interval) {
      // Create temp time from time1 and interval
      $ttime = strtotime('+1 ' . $interval, $time1);
      // Set initial values
      $add = 1;
      $looped = 0;
      // Loop until temp time is smaller than time2
      while ($time2 >= $ttime) {
        // Create new temp time from time1 and interval
        $add++;
        $ttime = strtotime("+" . $add . " " . $interval, $time1);
        $looped++;
      }
 
      $time1 = strtotime("+" . $looped . " " . $interval, $time1);
      $diffs[$interval] = $looped;
    }
 
    $count = 0;
    $times = array();
    // Loop thru all diffs
    foreach ($diffs as $interval => $value) {
      // Break if we have needed precission
      if ($count >= $precision) {
	break;
      }
      // Add value and interval 
      // if value is bigger than 0
      if ($value > 0) {
	// Add s if value is not 1
	if ($value != 1) {
	  $interval .= "s";
	}
	// Add value and interval to times array
	$times[] = $value . " " . $interval;
	$count++;
      }
    }
 
    // Return string with times
    return implode(", ", $times);
  }

$findEmployees = mysql_query("SELECT hr_employeeinfo.respid, doh FROM pr_resp LEFT JOIN hr_employeeinfo ON pr_resp.respid = hr_employeeinfo.respid WHERE pr_resp.hidden = '0' AND activeEmployee = '1' and employeeid > '0'") or die("Cannot get employees: " . mysql_error());

echo '<strong>Total Active Employees: ' . mysql_num_rows($findEmployees) . '</strong><br /><br />';

if(mysql_num_rows($findEmployees) > 0)
	{
	while($row = mysql_fetch_assoc($findEmployees))
		{
		echo $row['respid'] . ' - ' . $row['doh'] . ' - ' . employeeLength($row['doh'], $today) . '<br />';
		}
	}

Open in new window

0
Comment
Question by:t3chguy
[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
  • 3
7 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39709435
You will have some imprecision here because years and months do not have exact numbers of days.  Have a look at practical application number 3 in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 1

Author Comment

by:t3chguy
ID: 39709516
So in doing this...I was able to convert this to days, which is 1926.04054 days.  How can I convert this to years, months, weeks, and days?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39709535
There is no reliable way to get it completely right.  Maybe you can get it close, but you will have to accept that a year, plus or minus a day or two is "close enough."
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39709538
I recommend converting each individual tenure to days, which you can do with builtin php functions.  Then add up and average the total days.  Then if you wish, you can convert back to years/months/days.

For your purposes, just use a year as 365 days, so divide your 1926 by 365 to get years.  Take the remainder and divide by 12 to get months.  The remainder of that is days.
0
 
LVL 1

Author Comment

by:t3chguy
ID: 39709547
That is what I've done, just questioning the conversion process.  And certainly close enough is close enough.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39709572
Try this.  But understand that there are all kinds of edge cases where your computations will break down.  Here are some of the things that will make this impossible to achieve with perfect accuracy.

How many days in a year?  How about leap year?

How many days in a month?  January has 31, February has 28, or maybe 29 (25% of the time)

How many seconds in an hour?  That one's easy, except at the change of Daylight Savings Time when the number of seconds goes to either zero or 7200.  Unless you're not in an area that observes DST.

Sorry, but there is just no perfect answer.  "Close" is about all you can manage.  One strategy might be this...

if it's less than 30 days, call it in days.
If it's more than 30 days, call it in "months" by dividing by 30
If it's more than 365 days, call it in "years"... etc.

<?php // RAY_temp_t3chguy.php
error_reporting(E_ALL);
echo '<pre>';

// 1926.04054 days
// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28315012.html#a39709516


// STARTING WITH THIS NUMBER OF DAYS
$days  = 1926.0454;

// COMPUTE YEARS
$years = number_format(floor($days / 365.0));

// GET THE REMAINDER
$rem   = $days % 365.0;

// COMPUTE MONTHS FROM THE REMAINDER
$mnths = number_format(floor($rem / 30.0));

echo PHP_EOL . "FROM $days DAYS, WE GET $years YEARS AND $mnths MONTHS";

Open in new window

0
 
LVL 1

Author Closing Comment

by:t3chguy
ID: 39709627
That worked great, thank you so much.
0

Featured Post

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

765 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