Time Service Calculation
Posted: Thu Apr 26, 2007 10:07 am
I am having problems with the time service calculation below. If I run the calculation for a customer that has 20 reports, the calculation runs quickly. However, when I run the calculation for a customer that has 100 or more (roughly) the calculation takes 10 to 20 seconds, which is slow loading time. I was wondering if there is anything in my code that I can change to make this calculation more efficient.
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).
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;
?>