This calculation takes the number of cases that have been ordered in a certain time period (specified by $start_date and $end_date) and how many have been completed and calculates how many days as an average it took for each one individually to be completed. Then it takes those totals and gives one large total for all of the cases ordered and completed for a 30 day time period. ($row["control_number"] is the identifier for the row of data, each row has a control_number (ex: report number).
Code: Select all
<?php
$start_date = strtotime("-30 day");
$start_date = date("Y-m-d", $start_date);
$end_date = date("Y-m-d");
$result = mysql_query("SELECT * FROM cases WHERE customer='" . $_SESSION["user"] . "' AND date_completed BETWEEN '$start_date' AND '$end_date' ORDER BY control_number");
$row = mysql_fetch_array($result);
$ts_total_num = "1";
while($row = mysql_fetch_array($result)) {
$ts_total_num ++;
$result2 = mysql_query("SELECT * FROM cases WHERE control_number='" . $row["control_number"] . "'");
$row2 = mysql_fetch_array($result2);
$result3 = mysql_query("SELECT DATEDIFF('" . $row2["date_completed"] . "','" . $row2["date_ordered"] . "') AS time_service FROM cases WHERE control_number='" . $row["control_number"] . "'");
$row3 = mysql_fetch_array($result3);
$time_service = $row3["time_service"];
$ts_total_amount = $ts_total_amount + $time_service;
}
$average = $ts_total_amount / $ts_total_num;
$average = number_format($average, "2");
echo $average;
?>