Page 1 of 1

PHP Calculation to MySQL Query

Posted: Mon Aug 13, 2007 6:51 pm
by icesolid
I was just wondering if there is any way to perform this calculation in an simplier/smaller MySQL query.

Here is the calculation:

Code: Select all

$result = mysql_query("SELECT * FROM cases WHERE $sort AND date_completed BETWEEN '$y-01' AND '$y-02' ORDER BY id");
$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 id='" . $row["id"] . "'");
    $row2 = mysql_fetch_array($result2);

    $result3 = mysql_query("SELECT DATEDIFF('" . $row2["date_completed"] . "','" . $row2["date_ordered"] . "') AS time_service FROM cases WHERE id='" . $row["id"] . "'");
    $row3 = mysql_fetch_array($result3);

    $time_service = $row3["time_service"];
    $ts_total_amount = $ts_total_amount + $time_service;
}

$january = $ts_total_amount / $ts_total_num;
This calculation calculates time service for the month of January and prints a total. It takes every case ordered in January and then finds the date difference between each cases date ordered and date completed and gets a total number of days. Then it divides the total number of days by the number of cases ordered in that month and produces an average time service for that month.

Posted: Mon Aug 13, 2007 7:02 pm
by VladSun
Untested!

Code: Select all

SELECT AVG(DATEDIFF(date_completed, date_ordered))
FROM cases 
WHERE $sort AND date_completed BETWEEN '$y-01' AND '$y-02' 
PS: Your code shows cases *completed* in Januaray, not the ordered ones ...
If you want AVG of the ordered cases you should use date_ordered in the where clause.