MySQL: Count no of consecutive week days from datetime column

Question

I have the below table:

studentid VARCHAR(12)
latetime DATETIME
attendance CHAR(1)

latetime only have weekdays.

Some of the days the students will have "Parents letter" indicated by V for attendance column.

I need to group these attendance column V by consecutive week days. Then count these occurrences. Each group of consecutive days are counted as 1 letter.

My SQLFIDDLE: http://sqlfiddle.com/#!2/55d5b/1

This SQLFIDDLE sample data should return

STUDENTID   LETTERCOUNT
a1111           3
b2222           2


a1111 - 3 counts
-----
1. 2014-01-02
2. 2014-01-27
2. 2014-01-29 and 2014-01-30

b2222 - 2 counts
-----
1. 2014-01-02 and 2014-01-03
2. 2014-01-24, 2014-01-27 and 2014-01-28

I tried various methods from the below SO without any proper result yet:

How to GROUP BY consecutive data (date in this case)

MySQL: group by consecutive days and count groups

I can do this programatically in PHP by looping through the results and manually checking for each record + its next date. But i was trying to acheive the same with SQL.

Any help / direction towards finding a solution will be much appreciated.

Do you think your data set is properly representative of the problem at hand? — Strawberry, Feb 03 '14 at 14:21
I don't think "weekdays" is sufficient, because it doesn't take holidays into account Do you have a calendar of expected attendance days? — Gordon Linoff, Feb 03 '14 at 14:29
This is a simplified set of data with unnecessary data and columns are removed. — Vijay Vj, Feb 03 '14 at 14:34
I can create another table with expected attendance days. Currently holidays are filtered in PHP code. — Vijay Vj, Feb 03 '14 at 14:36

score 0 · Answer 1 · edited May 23 '17 at 11:56

This is derived from one of the answers in MySQL: group by consecutive days and count groups. I added the WITH ROLLUP option to get the letter count into the same query, and used GROUP_CONCAT to show all the dates. I made the INTERVAL conditional on the weekday, to skip over weekends; holidays aren't taken into account, though.

In my version of the fiddle I changed the latetime column to date, so I could remove all the DATE() functions from the SQL.

SELECT studentid, IFNULL(dates, '') dates, IF(dates IS NULL, lettercount, '') lettercount
FROM (
    SELECT studentid, dates, COUNT(*) lettercount
    FROM (
        SELECT v.studentid,
          GROUP_CONCAT(latetime ORDER BY latetime SEPARATOR ', ') dates
        FROM
          (SELECT studentid, latetime,
                @start_date := IF(@last_student IS NULL OR @last_student <> studentid,
                         1,
                         IF(@last_latetime IS NULL
                            OR (latetime - INTERVAL IF(WEEKDAY(latetime) = 0, 3, 1) DAY) > @last_latetime, latetime, @start_date)) AS start_date,
                @last_latetime := latetime,
                @last_student := studentid
           FROM
             studentattendance, (SELECT @start_date := NULL, @last_latetime := NULL, @last_student := NULL) vars
           WHERE attendance = 'V'
           ORDER BY
             studentid, latetime
          ) v
        GROUP BY
          v.studentid, start_date) x
    GROUP BY studentid, dates WITH ROLLUP) y
WHERE studentid IS NOT NULL
ORDER BY studentid, dates

http://sqlfiddle.com/#!2/6c944/12

MySQL: Count no of consecutive week days from datetime column

1 Answers1