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]

  On 12/23/2019 at 5:10 PM, 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;

 

Expand  

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]

  On 12/23/2019 at 5:35 PM, Heartstrings said:

It would help to see what your db data  looks like

Expand  

 

[Barand]

  On 12/23/2019 at 5:10 PM, Barand said:

It would help to see what your db data  looks like

Expand  

 

[Heartstrings]

  On 12/23/2019 at 5:37 PM, Barand said:

 

Expand  

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

[Barand]

Oh great! I love pictures.

  On 12/23/2019 at 5:35 PM, Heartstrings said:

that SELECT statement is giving me null

Expand  

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

[Heartstrings]

  On 12/23/2019 at 5:49 PM, Barand said:

Oh great! I love pictures.

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

Expand  

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]

  On 12/23/2019 at 6:08 PM, 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 |
+------+----------+----------+---------------+

 

Expand  

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]

  On 12/23/2019 at 6:16 PM, 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 |
+------+----------+----------+---------------+

 

Expand  

Thank you sir!