Group database rows in 15 minute intervals and sum other column totals

Question

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?

Possible duplicate of [Group DateTime into 5,15,30 and 60 minute intervals](http://stackoverflow.com/questions/9814930/group-datetime-into-5-15-30-and-60-minute-intervals) — , May 11 '16 at 02:40
Yes data just there for format sake, but i guess thats a requirement that could be for 30 or 15 minute intervals, just trying to start with 15 minute intervals first — James_Inger, May 11 '16 at 02:41

Julie Pelletier · Answer 1 · 2016-05-11T03:18:21.830

1

Here is the SQL I would use:

SELECT dte = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CallDateTime) / 15 * 15, 0) AS CallPeriod, SUM(IsAnswered) AS IsAnswered, SUM(IsLost) AS IsLost
FROM vwCustomReportingCallsMain 
WHERE DDI = '2399' AND datediff(day, CallDateTime, '2016-05-10') = 0 
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CallDateTime) / 15 * 15, 0)
ORDER BY dte ASC;

Please note that I don't have an MS SQL server accessible to test it but it should be really close.

edited May 11 '16 at 03:18

answered May 11 '16 at 02:47

Julie Pelletier

1,740
1
10
18

im getting an error when running - "Incorrect parameter count in the call to native function 'DATEDIFF'", which is referring to the first instance thereof, any idea's ? – James_Inger May 11 '16 at 02:52
I just copied the first datediff from your own query. – Julie Pelletier May 11 '16 at 02:55
This part is failing - DATEDIFF(MINUTE, 0, CallDateTime) / 15 * 15, 0) – James_Inger May 11 '16 at 02:56
So change to the following ?? SELECT dte = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CallDateTime / 15 * 15, 0) AS CallPeriod, SUM(IsAnswered) AS IsAnswered, SUM(IsLost) AS IsLost – James_Inger May 11 '16 at 03:02
Sorry, I thought it would work just like that but without any way of testing it, the issue could be a tiny mistake but I'm stuck. – Julie Pelletier May 11 '16 at 03:26

Nami Yamamoto · Answer 2 · 2016-05-11T03:33:05.223

Here is a PHP solution:

You can round the current time to the closest 15 minutes using the following code:

$time = strtotime('May 10 2016 08:55:01:000AM');
$rounded = round($time / (15 * 60)) * (15 * 60);

So for you it would something like:

$sql = "SELECT CallID, CallDateTime, IsLost, IsAnswered FROM vwCustomReportingCallsMain WHERE DDI = '2399' AND datediff(day, CallDateTime, '2016-05-10') = 0 ORDER BY CallDateTime ASC";

$callDataArray = array();
foreach ($dbh->query($sql) as $row)
    {
        if($row['CallID'] != $last) {
            $time = strtotime($row['CallDateTime']);
            $date = round($time / (15 * 60)) * (15 * 60);
            if (!isset($callDataArray[$date])) {
                $callDataArray[$date] = array(
                    'isLost' => $row['IsLost'],
                    'isAnswered' => $row['IsAnswered'],
                );
            } else {
                $callDataArray[$date]['isLost'] = $callDataArray[$date]['isLost'] + row['IsLost'];
                $callDataArray[$date]['isAnswered'] = $callDataArray[$date]['isAnswered'] + row['IsAnswered'];
            }
        }
        $last = $row['CallID'];
    }

You can then loop through the $callDataArray like so:

<table><tr><th>Date</th><th>Answered</th><th>Lost</th></tr>
<?php
foreach($callDataArray as $date => $data) {
    echo '<tr>';
    echo '<td>' . date('jS \of F Y h:i:s A', $date) . '</td>';
    echo '<td>' . $data['isAnswered'] . '</td>';
    echo '<td>' . $data['isLost'] . '</td>';
    echo '</tr>';
}
?>
</table>

ok so how would i display the array if I don't know the key values ? — James_Inger, May 11 '16 at 03:03

score -1 · Accepted Answer · answered May 11 '16 at 03:08

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

ok just a quick question - how would i use this to round to nearest day if date range is beetwen 2 dates, tried using 1440 instead of 30 - just rounds to 60 minutes instead — James_Inger, Jun 28 '16 at 01:26
That's because date('i', $timestring), here 'i' is minutes which is relative to hours. So 'i' can never be higher than 60. — Ghulam Ali, Jun 28 '16 at 09:46

Group database rows in 15 minute intervals and sum other column totals

3 Answers3