Jump to content
Heartstrings

Organizing Data By Weeks

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! 

Share this post


Link to post
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

Share this post


Link to post
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. 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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     |   

 

Share this post


Link to post
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 |
+------+----------+----------+---------------+

 

Share this post


Link to post
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? 

Share this post


Link to post
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

Share this post


Link to post
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! 

Share this post


Link to post
Share on other sites

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.