Tricky math with dates

I don't know if this is possible

[RussellC]

I am in a services industry so I am trying to calculate average turnaround times for all "jobs" that come in house. I have a datebase where I have a $jobin and $jobout variable, what I have come up with so far works but includes weekends. I am wondering if there is a way to take all weekends out of the equation. This seems impossible to me but it may be able to be done. Here is what I have so far to calculate the average turnaround time.

$howmany = 0;
$adddays = 0;
$rectime = "SELECT jobdate, completedate FROM jobs WHERE completedate!= 'NULL'";
$qrectime = @mysql_query($rectime);
while ($rect = @mysql_fetch_array($qrectime)) {
$howmany = $howmany + 1;

$datein = $rect["jobdate"];
$jobdone = $rect["completedate"];

$dateinu = strtotime("$datein");
$jobdoneu = strtotime("$jobdone");

$turnaround = $jobdoneu - $dateinu;
$ta = $turnaround / 86400;
$ta2 = round($ta, 2);
$adddays = $adddays + $ta2;
}

$avgturnaround = round($adddays / $howmany, 2);
echo("Average Turnaround Time (including weekends) = $avgturnaround Days");

This works perfectly, but if I could cancel out weekends I would get a much more accurate number for setting goals, etc...

Thanks for the help,

-Russell

[dmorison]

Could you traverse your list of days, and use the date() function to get the day of week for each day; incrementing a counter only if the day is a weekday...something like:

$dateinu = strtotime("$datein"); 
$jobdoneu = strtotime("$jobdone"); 
$avgturnaround = 0; 
for($i=$dateinu;$i<=$jobdoneu;$i+=86400) 
{ 
 $dayofweek = date("w",$i); 
  
 // if not Sunday and not Saturday increment $avgturnaround 
 if (($dayofweek!= 0) && ($dayofweek!= 6)) $avgturnaround++;  
} 

Check how this works carefully; there may be some "off by one" condition, but it should give you some ideas.

[ergophobe]

Beginning with mysql 4.1.1, you can just do this:

SELECT AVG(DATEDIFF(job_in, job_out)) as turnaround FROM table1;

[ergophobe]

With mysql 3.x, you can do this

$q = "SELECT AVG((TO_DAYS(job_out) - TO_DAYS(job_in))) as average FROM jobs";
$r = mysql_query($q);
$avg_turnaround = mysql_result($r, 0);

[RussellC]

Nice tips guys, thanks! I got it working!