0

I have a query that prints out times and averages. The only problem is - if nothing happened in the time period of the 6 weeks that I'm running this for - it skips right over it... I need that 15 minute period to have a NULL or zero in its place. Here is my Query: 

$staffing_qry = "SELECT MAKETIME(hour(opened_dt),floor(minute(opened_dt)/15)*15,0) AS time, ";
$staffing_qry .= "ROUND(COUNT(*)/COUNT(DISTINCT DATE(opened_dt)),1) AS calls, ";
$staffing_qry .= "ROUND(AVG(work_time)/60,1) AS work, ";
$staffing_qry .= "ROUND(AVG(tele_time)/60,1) AS tele, ";
$staffing_qry .= "ROUND(AVG(comm_time)/60,1) AS comm, ";
$staffing_qry .= "ROUND(IFNULL(COUNT(*)/COUNT(DISTINCT DATE(opened_dt)),0)/3,1) AS techs ";
$staffing_qry .= "FROM detail_head ";
$staffing_qry .= "LEFT JOIN detail_detail ON detail_detail.detail_head_uid = detail_head.detail_head_uid ";
$staffing_qry .= "WHERE dayname(opened_dt) = $dow_option $staffing_option $proactive_option $incoming_option ";
$staffing_qry .= "AND (DATE(opened_dt) >= (CURDATE() - INTERVAL 42 DAY))";
$staffing_qry .= "GROUP BY (hour(opened_dt)*100)+floor(minute(opened_dt)/15) ";

Suggestions?

000
  • 26,951
  • 10
  • 71
  • 101
brandoncluff
  • 303
  • 1
  • 5
  • 19
  • There is an entire section of Stack Overflow for these kinds of questions. It is called "Gaps and Islands". I have added the tag to your question to get a wider audience. See here: http://stackoverflow.com/questions/tagged/gaps-and-islands?sort=frequent – 000 Jun 21 '13 at 14:27
  • Ok - I can see how this question fits into that category... Thank you! Do you have a solution for my specific query? Many of those are specific to the query given. – brandoncluff Jun 21 '13 at 16:57
  • Sorry I do not. I just know where to categorize it. – 000 Jun 21 '13 at 17:25
  • Just scanned through this, but this should work: http://stackoverflow.com/questions/5608740/mysql-how-to-use-coalesce – We0 Jun 24 '13 at 08:04

1 Answers1

0

RESOLVED: 

if(isset($_REQUEST['date_range'])) {
        $date_range_option = (integer) $_REQUEST['date_range'];
}
brandoncluff
  • 303
  • 1
  • 5
  • 19