GOAL: I am trying to retrieve the sum of data on a weekly basis between a start and end-date (provided by the user).
Issue: **When retrieving data between a date range involving 2019-2020, the first week of 2020 is skipped.
“`
//Retrieve count of attendance, no shows and cancellations per user selected category and sort by week number
$q = “SELECT YEARWEEK(`start_time`, 0) AS weekno,
SUM(`is_no_show` = 0 AND `is_cancelled` = 0) as attended,
SUM(`is_no_show` = 1) AS no_shows,
SUM(`is_cancelled` = 1)AS cancelled
FROM `myTable`
WHERE (`start_time` > :start_date AND `start_time` < :end_date)
AND category LIKE :cohort_type
GROUP BY weekno”;
try {
$stmt = $dbh->prepare($q);
$stmt->bindParam(‘:start_date’, $start_date);
$stmt->bindParam(‘:end_date’, $end_date);
$stmt->bindParam(‘:cohort_type’, $cohort_type, PDO::PARAM_STR);
$stmt->execute();
$q_result = $stmt->fetchAll(PDO::FETCH_ASSOC);
if($q_result) {
if(count($q_result) > 1) {
// good records
}else{
// no records
}
}else{
// query failed.
}
echo json_encode(array(‘success’ => 1,’msg’ => $q_result));
}
catch(PDOException $e) {
echo json_encode(array(‘success’=> 1, ‘msg’=>$e->getMessage()));
exit;
}
General DB Structure:
`+————+———–+———–+
| start_time | no_shows | cancelled |
+————+———–+———–+
| 2019-12-20 | 1 | 0 |
| 2019-12-21 | 0 | 0 |
| 2019-12-22 | 0 | 1 |
Thank you!!