Organizing Data By Weeks

[Heartstrings]

So I'm calling in data from a DB where each record is associated with a (column) 'start_time', 'no_shows', 'cancelled', and 'attended' 

I want to go through the results of this SELECT and count the number of no shows, cancellations and attended on a WEEKLY basis (based on the start_time). How can I achieve this? 

The user will provide a date range and the DB will select the records based on that date range. Now this date range will then have to be split by weeks and then get the count. I'm totally stuck on the counting by weeks part. This is what I have so far: 

All it does is just organize the data into separate arrays without any counting. Attached is a preview of the output. 

// Multidimensional Array with [boolean][week #]
  $no_shows_weekly = [
    'no_show' => [],
    'week' => []
  ];
  $cancelled_weekly = [
    'cancelled' => [],
    'week' => []
  ];
  $attended_weekly = [
    'attended' => [],
    'week' => []
  ];

  foreach($result as $r) {
    $start_time = new DateTime($r['start_time']);
    if($r['is_no_show'] == 0 && $r['is_cancelled'] == 0) {
        array_push($attended_weekly['attended'], 1);
        array_push($attended_weekly['week'], date_format($start_time, "W"));
    }
    else {
      array_push($attended_weekly['attended'], 0);
      array_push($attended_weekly['week'], date_format($start_time, "W"));
    }
    array_push($no_shows_weekly['no_show'], $r['is_no_show']);
    array_push($no_shows_weekly['week'], date_format($start_time, "W"));
    array_push($cancelled_weekly['cancelled'], $r['is_cancelled']);
    array_push($cancelled_weekly['week'], date_format($start_time, "W"));
  }
  echo json_encode(array(
       'success'=> 1,
       'msg'=>
         array(
           'No Shows' => $no_shows_weekly,
           'Cancellations' => $cancelled_weekly,
           'Attendend' => $attended_weekly
         )
    ));

Any suggestions or help is greatly appreciated! 

[Barand]

It would help to see what your db data  looks like EG, is it

+------------+-----------+-----------+----------+
| start_time |  no_shows | cancelled | attended |
+------------+-----------+-----------+----------+
| 2019-12-20 |     1     |     0     |     0    |
| 2019-12-21 |     0     |     0     |     1    |
| 2019-12-22 |     0     |     1     |     0    |

If so, then

SELECT weekofyear(start_time) as weekno
     , SUM(no_shows = 1) as no_shows
     , SUM(cancelled = 1) as cancelled
     , SUM(attended = 1) as attended
FROM mytable
WHERE start_time BETWEEN ? AND ?
GROUP BY weekno;

 

Edited December 23, 2019 by Barand

[Heartstrings]

24 minutes ago, Barand said:

It would help to see what your db data  looks like EG, is it

+------------+-----------+-----------+----------+
| start_time |  no_shows | cancelled | attended |
+------------+-----------+-----------+----------+
| 2019-12-20 |     1     |     0     |     0    |
| 2019-12-21 |     0     |     0     |     1    |
| 2019-12-22 |     0     |     1     |     0    |

If so, then

SELECT weekofyear(start_time) as weekno
     , SUM(no_shows = 1) as no_shows
     , SUM(cancelled = 1) as cancelled
     , SUM(attended = 1) as attended
FROM mytable
WHERE start_time BETWEEN ? AND ?
GROUP BY weekno;

 

Yes, that's what my DB looks like
Hmm..that SELECT statement is giving me null even though I replaced the variables with the correct names. 

[Barand]

Just now, Heartstrings said:

It would help to see what your db data  looks like

 

[Barand]

26 minutes ago, Barand said:

It would help to see what your db data  looks like

 

[Heartstrings]

1 minute ago, Barand said:

 

DB structure:
https://imgur.com/a/DdyTqiE

[Barand]

Oh great! I love pictures.

13 minutes ago, Heartstrings said:

that SELECT statement is giving me null

Where? You're not giving many clues if you want help.

[Heartstrings]

9 minutes ago, Barand said:

Oh great! I love pictures.

Where? You're not giving many clues if you want help.

When I ran this query I get: 

 

+------------+-----------+-----------+
| weekno     |  no_shows | cancelled | 
+------------+-----------+-----------+
| NULL       |     0     |     0     |   

 

[Heartstrings]

Nevermind I just needed to use ` instead of ' for the column names lol. Thank you so much. 

[Barand]

Weird!

When I ran this

based on https://imgur.com/a/kYwmuO1

I got

+------+----------+----------+---------------+
| wkno | attended | no_shows | cancellations |
+------+----------+----------+---------------+
|   40 |        2 |        0 |             0 |
|   41 |        0 |        1 |             1 |
|   42 |        1 |        0 |             0 |
|   43 |        0 |        1 |             0 |
+------+----------+----------+---------------+

 

[Heartstrings]

1 minute ago, Barand said:

Weird!

When I ran this

based on https://imgur.com/a/kYwmuO1

I got

+------+----------+----------+---------------+
| wkno | attended | no_shows | cancellations |
+------+----------+----------+---------------+
|   40 |        2 |        0 |             0 |
|   41 |        0 |        1 |             1 |
|   42 |        1 |        0 |             0 |
|   43 |        0 |        1 |             0 |
+------+----------+----------+---------------+

 

Interesting! So since I don't have a separate attended column..(it's based on whether no_shows & cancellations are both 0), can I just add them up and subtract from total to get attended? 

[Barand]

I was assuming that a report_filed meant they attended. But, yes, you could

mysql> SELECT weekofyear(start_time) as wkno
    ->      , SUM(is_no_show=0 AND is_cancelled=0) as attended
    ->      , SUM(is_no_show=1) as no_shows
    ->      , SUM(is_cancelled=1) as cancellations
    -> FROM heartstring
    -> GROUP BY wkno;
+------+----------+----------+---------------+
| wkno | attended | no_shows | cancellations |
+------+----------+----------+---------------+
|   40 |        2 |        0 |             0 |
|   41 |        0 |        1 |             1 |
|   42 |        1 |        0 |             0 |
|   43 |        0 |        1 |             0 |
+------+----------+----------+---------------+

 

[Heartstrings]

39 minutes ago, Barand said:

I was assuming that a report_filed meant they attended. But, yes, you could

mysql> SELECT weekofyear(start_time) as wkno
    ->      , SUM(is_no_show=0 AND is_cancelled=0) as attended
    ->      , SUM(is_no_show=1) as no_shows
    ->      , SUM(is_cancelled=1) as cancellations
    -> FROM heartstring
    -> GROUP BY wkno;
+------+----------+----------+---------------+
| wkno | attended | no_shows | cancellations |
+------+----------+----------+---------------+
|   40 |        2 |        0 |             0 |
|   41 |        0 |        1 |             1 |
|   42 |        1 |        0 |             0 |
|   43 |        0 |        1 |             0 |
+------+----------+----------+---------------+

 

Thank you sir!