Php versions - Consecutive Absent Students Excluding Holidays

user3734397
November 12, 2023
309 views
0 votes
2 Answers

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");

Tags: gaps-and-islands mysql

Answers

One approach would be to use session variables, and count absent days. Reset the counter on present days or when student changes, e.g.

select student_id, timestamp,
       case when status = 'A' and student_id = @student then @absent := @absent + 1
            when status = 'A' and student_id <> @student then @absent := 1
            when status = 'P' then @absent := 0 end as absent,
       case when student_id <> @student then @student := student_id end as tracking_student
from attendance, (select @absent := 0, @student := -1) as init
where status in ('A', 'P')
order by student_id, timestamp

See this db-fiddle

student_id	timestamp	absent	tracking_student
124	2023-11-05	0	124
124	2023-11-06	1	null
124	2023-11-07	2	null
124	2023-11-08	3	null
124	2023-11-11	4	null
124	2023-11-12	0	null
125	2023-11-05	1	125
125	2023-11-06	0	null
125	2023-11-07	0	null
125	2023-11-08	0	null
125	2023-11-11	0	null
125	2023-11-12	0	null

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 (@prev_status := 'P') AND (@grp := 0), null) AS reset,
        @grp := IF (@prev_status = 'P' AND status = 'A', @grp + 1, @grp) AS grp,
        @prev_status := status
    FROM attendance
    JOIN (SELECT @prev_status := 'P', @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;

Outputs:

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

Here’s a db<>fiddle.

Please signup or login to give your own answer.

Click here to cancel reply.

Php versions – Consecutive Absent Students Excluding Holidays

Answers