how to display avg, sum, etc?

[mcerveni]

I'm trying to calculate the average Efficiency for each day and also the total Efficiency of the month.

 

So far i have this:

 

 

my code is :

 

$sql = "SELECT * FROM import_stats WHERE EmpID = '$searchreq' || AgentName = '$searchreq' ORDER BY date";
$result = mysql_query($sql);


$sql2 = "SELECT TeamLeader, AgentName, EmpID, AvayaID, date, SUM(StaffedTime), AHT , ACDCalls,  SUM(ACDTime) , AvgACDTime, SUM(ACWTime), SUM(AvgACWTime),  HoldCalls, HoldTime,  AvgHoldTime,  OutboundCalls , SUM(OutboundTime) , SUM(AvailTime),  RingTime,  AvgRingTime,  AVG(utilization), SUM(aux1), SUM(aux2), SUM(aux7), SUM(aux8), SUM(aux9) FROM import_stats WHERE EmpID = '$searchreq' || AgentName = '$searchreq' GROUP BY AgentName";

//not having a $result2, the first record will only display, not anything after that. 
$result2 = mysql_query($sql2) or die(mysql_error());
$row2= mysql_fetch_array($result2);


//HOW EFFICIENCY IS CALCULATED:
//ACD Time + ACW Time + OB Time + Avail Time + AUX 1 + AUX 2 + AUX 7 + AUX 8 + AUX 9 ) / Staff Time
$acdTime = $row2['SUM(ACDTime)'];
$acwTime = $row2['SUM(ACWTime)'];
$outboundTime = $row2['SUM(OutboundTime)'];
$availTime = $row2['SUM(AvailTime)'];
$aux1 = $row2['SUM(aux1)'];
$aux2 = $row2['SUM(aux2)'];
$aux7 = $row2['SUM(aux7)'];
$aux8 = $row2['SUM(aux8)'];
$aux9 = $row2['SUM(aux9)'];
$staffedTime = $row2['SUM(StaffedTime)'];

$eff = $acdTime + $acwTime + $outboundTime + $availTime + $aux1 + $aux2 + $aux7 + $aux8 + $aux9;
$total_eff = $eff / $staffedTime; 



$utilization = $row2['AVG(utilization)'];
$avg_acwTime = $row2['SUM(AvgACWTime)'];

if(mysql_num_rows($result) == 0) { 
echo 'No records found for: <font color="blue"> '.$_GET['req'];  ' </font>';
}
else {

 

the rest for displaying the table is:

 

<table id="agentStatsTable">

<tr> <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="date" onclick="sort(id,name)" >  Date </a> </th>  <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="AHT " onclick="sort(id,name)" >  AHT </a> </th> <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="ACDCalls " onclick="sort(id,name)" > ACD Calls </a> </th>  
  <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="ACWTime " onclick="sort(id,name)" >  ACW Time </a> </th> <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="AvgACWTime " onclick="sort(id,name)" > Avg. ACW Time </a> </th>  <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="HoldCalls" onclick="sort(id,name)" > Hold Calls </a> </th>   <th>  <a href="#" id="<?php echo $row2['EmpID'];?>" name="AvgHoldTime " onclick="sort(id,name)" >Avg. Hold Time </a> </th>  <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="OutboundCalls " onclick="sort(id,name)" > Outbound Calls </a> </th>  
<th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="OutboundTime " onclick="sort(id,name)" > Outbound Time </a> </th>  <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="AvailTime " onclick="sort(id,name)" > Avail Time </a> </th>   <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="AvgRingTime" onclick="sort(id,name)" >  Avg. Ring Time </th>  <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="efficiency" onclick="sort(id,name)" > Efficiency </a> </th> <th> <a href="#" id="<?php echo $row2['EmpID'];?>" name="showMore" onclick="sort(id,name)" > Aux's </a> </th> 
</tr>

<?php while($row= mysql_fetch_array($result)) { //loop record. ?>


<tr> <td> <?php echo $row['date'];?>  </td>   

<?php if ($row['AHT'] >= "00:19:00") { ?>
<td style="color:#c02525"> <?php echo $row['AHT'];?>  </td>
<?php }
else { ?>
<td style="color:green"> <?php echo $row['AHT'];?>  </td>
<?php } ?>


<td> <?php echo $row['ACDCalls'];?>  </td>   </td>  
<td> <?php echo $row['ACWTime'];?>  </td>  


<?php if ($row['AvgACWTime'] >= "00:03:00") { ?>
<td style="color:#c02525"> <?php echo $row['AvgACWTime'];?>  </td>
<?php }
else { ?>
<td style="color:green"> <?php echo $row['AvgACWTime'];?>  </td>
<?php } ?>




<td> <?php echo $row['HoldCalls'];?>  </td>

<?php if ($row['AvgHoldTime'] >= "00:03:00") { ?>
<td style="color:#c02525"> <?php echo $row['AvgHoldTime'];?>  </td>
<?php }
else { ?>
<td style="color:green"> <?php echo $row['AvgHoldTime'];?>  </td>
<?php } ?>



<td> <?php echo $row['OutboundCalls'];?>  </td>  <td> <?php echo $row['OutboundTime'];?>  </td>  
<td> <?php echo $row['AvailTime'];?>  </td>   <td> <?php echo $row['AvgRingTime'];?>  </td> 

<?php if ($efficency <= "75") { ?>
<td style="color:#c02525"> <?php echo $efficency ?>%  </td>
<?php }
else { ?>
<td style="color:green"> <?php echo $efficency ?>%  </td>
<?php } ?>

<td> More </td> 

</tr>

<?php
}
?>

<?php
}

 

 

all i need to do is calculate the person's efficiency. and the formula for that is:

 

ACD Time + ACW Time + OB Time + Avail Time + AUX 1 + AUX 2 + AUX 7 + AUX / Staff Time

[Maq]

That's a lot of information, what are you specifically having trouble with?

[mcerveni]

Sorry lol..well all i am having trouble with is calculating the Efficiency. and the formula for that is:

 

ACD Time + ACW Time + OB Time + Avail Time + AUX 1 + AUX 2 + AUX 7 + AUX / Staff Time

 

I want to show that as a percentage.

[Zane]

SELECT TeamLeader, AgentName, EmpID, AvayaID, date, SUM(StaffedTime), AHT , ACDCalls,  SUM(ACDTime) , AvgACDTime, SUM(ACWTime), SUM(AvgACWTime),  HoldCalls, HoldTime,  AvgHoldTime,  OutboundCalls , SUM(OutboundTime) , SUM(),  RingTime,  AvgRingTime,  AVG(utilization), SUM(aux1), SUM(aux2), SUM(aux7), SUM(aux8), SUM(aux9) FROM import_stats WHERE EmpID = '$searchreq' || AgentName = '$searchreq' GROUP BY AgentName

 

All this is doing is getting a sum for each column..and a few averages here and there

if you want to go by what you are talking about

//HOW EFFICIENCY IS CALCULATED:

//ACD Time + ACW Time + OB Time + Avail Time + AUX 1 + AUX 2 + AUX 7 + AUX 8 + AUX 9 ) / Staff Time

 

then you'll need to SUM (add) ALL of those together like

SELECT (SUM(ACDTime, ACWTime, OutboundTime, AvailTime, aux1, aux2, aux7, aux8, aux9) / StaffedTime) as efficiencyFormula

 

Also, this will do nothing

$row2['SUM(ACDTime)'];

 

you can't call SQL in an array...that's PHP

you'll have to call $row['efficiencyFormula'];

 

and multiply by 100 to make it a percentage

 

[mcerveni]

Thank you for the advice!

 

i'll try that... but what you said about $row2['SUM(ACDTime)'];

it does add up all the ACDTime ...so i'm not too sure what you meant by it does nothing.

example:

 

$acdTime = $row2['SUM(ACDTime)'];

ACD Time: <?php echo round($acdTime);?>

 

that code will display: ACD Time: 171336

 

my question is how do i display that in this format: 00:00:00

 

[Maq]

You need to format it using the date() function.

[Zane]

but what you said about $row2['SUM(ACDTime)'];

it does add up all the ACDTime ...so i'm not too sure what you meant by it does nothing.

example:

 

$acdTime = $row2['SUM(ACDTime)'];

ACD Time: 

 

that code will display: ACD Time: 171336

 

 

damn...I learn something everyday...my bad

[mcerveni]

it's ok lol.. i'm working on that efficiency query right now...i'll let you know how i do with it..

 

and i know that i'd have to format it using date, but i'm kind of stuck on how? lol i mean.. i haven't tried yet but a little to confused on how to implement it with the value i already have..?

[mcerveni]

so..

 

$sql2 = "SELECT (SUM(ACDTime, ACWTime, OutboundTime, AvailTime, aux1, aux2, aux7, aux8, aux9) / StaffedTime) as efficiencyFormula FROM import_stats WHERE EmpID = '$searchreq' || AgentName = '$searchreq' GROUP BY AgentName";

 

will give me this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ACWTime, OutboundTime, AvailTime, aux1, aux2, aux7, aux8, aux9) / StaffedTime) ' at line 1

 

...? any suggestions ?

 

i have GROUP BY AgentName at the end of the query because i was using AVG(column) before ..but ..how should i change it so it works to what you were saying.