Jump to content

Organizing Data By Weeks


Heartstrings

Recommended Posts

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. 620203877_ScreenShot2019-12-23at11_59_56AM.thumb.png.bf8ff0a8234961bda8f94933038edd40.png

// 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! 

Link to comment
Share on other sites

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 by Barand
  • Great Answer 1
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Weird!

When I ran this

image.png.6fe4ef65452e9c1347ea8ef1f905706d.png

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 |
+------+----------+----------+---------------+

 

Link to comment
Share on other sites

1 minute ago, Barand said:

Weird!

When I ran this

image.png.6fe4ef65452e9c1347ea8ef1f905706d.png

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? 

Link to comment
Share on other sites

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 |
+------+----------+----------+---------------+

 

  • Thanks 1
Link to comment
Share on other sites

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! 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.