I am trying to group a multidimensional array into 15 minute increments with the following data. This is data from a Call Center Database for phone records:
CallID CallDateTime IsLost IsAnswered
82650 May 10 2016 08:55:01:000AM 0 1
82666 May 10 2016 09:03:13:000AM 0 1
82677 May 10 2016 09:07:45:000AM 0 1
82688 May 10 2016 09:12:19:000AM 0 1
82689 May 10 2016 09:14:42:000AM 0 1
82702 May 10 2016 09:22:08:000AM 0 1
82708 May 10 2016 09:26:26:000AM 0 1
82737 May 10 2016 09:39:42:000AM 0 1
82739 May 10 2016 09:43:17:000AM 0 1
82748 May 10 2016 09:45:22:000AM 0 1
82786 May 10 2016 09:59:26:000AM 0 1
82789 May 10 2016 10:00:21:000AM 0 1
82820 May 10 2016 10:26:52:000AM 0 1
82842 May 10 2016 10:37:14:000AM 0 1
82846 May 10 2016 10:39:04:000AM 0 1
82859 May 10 2016 10:48:30:000AM 0 1
82865 May 10 2016 10:50:18:000AM 0 1
82880 May 10 2016 10:56:47:000AM 0 1
82911 May 10 2016 11:07:30:000AM 0 0
82925 May 10 2016 11:15:34:000AM 0 1
82926 May 10 2016 11:16:08:000AM 0 1
82927 May 10 2016 11:16:17:000AM 0 1
82943 May 10 2016 11:23:16:000AM 0 1
82944 May 10 2016 11:25:54:000AM 0 1
82947 May 10 2016 11:26:48:000AM 0 1
82966 May 10 2016 11:33:27:000AM 0 1
83048 May 10 2016 12:14:39:000PM 0 1
That I get from an MSSQL Server Database, placing data in via the following PHP Code
$sql = "SELECT CallID, CallDateTime, IsLost, IsAnswered FROM vwCustomReportingCallsMain WHERE DDI = '2399' AND datediff(day, CallDateTime, '2016-05-10') = 0 ORDER BY CallDateTime ASC";
foreach ($dbh->query($sql) as $row)
{
if($row['CallID'] != $last) {
$callDataArray[] = array("CallID" => $row['CallID'], "CallDateTime" => $row['CallDateTime'], "IsLost" => $row['IsLost'], "IsAnswered" => $row['IsAnswered']);
}
$last = $row['CallID'];
}
The desired output would be like the following format somehow (data not correct)
Date/Time IsAnswered IsLost
May 10 2016 08:30 7 0
May 10 2016 09:00 8 0
May 10 2016 09:30 14 0
May 10 2016 10:00 11 0
May 10 2016 10:30 11 0
May 10 2016 11:00 13 0
May 10 2016 11:30 12 0
May 10 2016 12:00 11 0
May 10 2016 12:30 8 0
May 10 2016 13:00 20 0
May 10 2016 13:30 9 0
May 10 2016 14:00 10 0
May 10 2016 14:30 12 0
May 10 2016 15:00 8 0
May 10 2016 15:30 14 0
May 10 2016 16:00 12 0
May 10 2016 16:30 11 0
Any ideas on how to in PHP or even MSSQL PDO?
You can use the following code. It may not be perfect but it will give you an idea on how to proceed further.
function roundToNearest30($timestring) {
$minutes = date('i', $timestring);
return sprintf("%02d", $minutes - ($minutes % 30));
}
$totalArray = array();
$lastTime = NULL;
foreach($callDataArray as $callData){
$timeString = strtotime($callData['CallDateTime']);
$currentHour = date('H', $timeString);
$nearest30Minutes = roundToNearest30($timeString);
$nearestTime = "{$currentHour}:{$nearest30Minutes}";
//create array key based on nearest 30 minutes
if (!isset($totalArray[$nearestTime])){
//add the array element as it does not exist
$totalArray[$nearestTime] = array("TotalLost" => $callData['IsLost'], "TotalAnswered" => $callData['IsAnswered']);
}else{
//update they array element with previous data
$totalArray[$nearestTime]["TotalLost"] += $callData['IsLost'];
$totalArray[$nearestTime]["TotalAnswered"] += $callData['IsAnswered'];
}
}
foreach($totalArray as $nearest30 => $data){
echo "{$nearest30}, Total Lost: {$data['TotalLost']}, Total Answered: {$data['TotalAnswered']}";
echo "<br>";
}
Output:
08:30, Total Lost: 0, Total Answered: 1
09:00, Total Lost: 0, Total Answered: 6
09:30, Total Lost: 0, Total Answered: 4
10:00, Total Lost: 0, Total Answered: 2
10:30, Total Lost: 0, Total Answered: 5
11:00, Total Lost: 0, Total Answered: 6
11:30, Total Lost: 0, Total Answered: 1
12:00, Total Lost: 0, Total Answered: 1