Consecutive Absent Students Excluding Holidays

Question

I want to have 124 ID student to be retrieved since I want 4 days consecutive absent student within the last 8 days ignoring the weekend H. 124 student had been absent for 3 consecutive days before the weekend and he was also absent after the weekend on Monday so he is the one to be retrieve since consecutively absent for 4 days

I tried this which works fine but this query only works with MySQL 8.1 whereas I'm using an older version 5.6.41-84.1 and PHP version: 7.4.33.

How can I do this on my version of MySQL?

attendance_id	timestamp	student_id	status
1	2023-11-05	124	P
2	2023-11-05	125	P
3	2023-11-06	124	A
4	2023-11-06	125	P
5	2023-11-07	124	A
6	2023-11-07	125	P
7	2023-11-08	124	A
8	2023-11-08	125	P
9	2023-11-09	124	H
10	2023-11-09	125	H
11	2023-11-10	124	H
12	2023-11-10	125	H
13	2023-11-11	124	A
14	2023-11-11	125	P
15	2023-11-12	124	P
16	2023-11-12	125	P

$query = $this->db->query("
select *,
    student_id,
    min(timestamp) timestamp_start,
    max(timestamp) timestamp_end
from (
    select 
        t.*, 
        row_number() over(partition by student_id order by timestamp) rn1,
        row_number() over(partition by student_id, status order by timestamp) rn2
    from attendance t
) t
where status = A AND timestamp BETWEEN (CURRENT_DATE() - INTERVAL 8 DAY) AND CURRENT_DATE()
group by student_id, rn1 - rn2
having count(*) >= 4");

Answer 1

It was suggested that you should upgrade the best part of four years ago. You really should stop putting it off.

This is a horrible solution but it works, I think. It creates a grouping similar to that in your ROW_NUMBER based query:

SELECT
    student_id,
    MIN(timestamp) AS ts_start,
    MAX(timestamp) AS ts_end
FROM (
    SELECT attendance.*,
        IF (@prev_student <> student_id, (@prev_student := student_id) AND (@grp := 0), null) AS reset,
        @grp := IF (status = 'P', @grp + 1, @grp) AS grp
    FROM attendance
    JOIN (SELECT @prev_student := 0, @grp := 0) AS init
    WHERE status IN ('A', 'P')
    AND timestamp BETWEEN CURRENT_DATE - INTERVAL 8 DAY
                      AND CURRENT_DATE
    ORDER BY student_id, timestamp
) a
WHERE status = 'A'
GROUP BY student_id, grp
HAVING COUNT(*) >= 4;

The derived table is:

attendance_id	timestamp	student_id	status	reset	grp
1	2023-11-05	124	P	0	1
3	2023-11-06	124	A	null	1
5	2023-11-07	124	A	null	1
7	2023-11-08	124	A	null	1
13	2023-11-11	124	A	null	1
15	2023-11-12	124	P	null	2
2	2023-11-05	125	A	0	0
4	2023-11-06	125	P	null	1
6	2023-11-07	125	P	null	2
8	2023-11-08	125	P	null	3
14	2023-11-11	125	P	null	4
16	2023-11-12	125	P	null	5

And the full query outputs:

student_id	ts_start	ts_end
124	2023-11-06	2023-11-11

Here's a db<>fiddle.