Consecutive absences in MySQL
- by pepersview
Hi,
I have 2 tables in my db: students and absences. In students: id_student, s_name; and in absences: id_student, date, day(the day of the week as number-it's not so important);
I would like to get the students that were absent for more than 2 days consecutive and the dates.
At the moment I have the next query:
/*To control when the days are in the same week*/
SELECT id_student, date, dayname(date),date_add(date, INTERVAL 1 DAY) AS a,
dayname(date_add(date, INTERVAL 1 DAY)) AS an, date_add(date, INTERVAL 2 DAY) AS b,
dayname(date_add(date, INTERVAL 2 DAY)) AS bn
FROM absences AS a
WHERE id_student IN
(SELECT id_student FROM absences
WHERE id_student = a.id_student
AND date = date_add(a.date, INTERVAL 1 DAY) )
AND id_student IN
(SELECT id_student FROM absences
WHERE id_student = a.id_student
AND date = date_add(a.date,INTERVAL 2 DAY) )
UNION
/*To control when the days jump to the next week*/
SELECT id_student, date, dayname(date),date_add(date, INTERVAL 3 DAY) AS a,
dayname(date_add(date, INTERVAL 3 DAY)) AS an, date_add(date, INTERVAL 4 DAY) AS b,
dayname(date_add(date, INTERVAL 4 DAY)) AS bn
FROM absences AS a
WHERE id_student IN
(SELECT id_student FROM absences
WHERE id_student = a.id_student
AND date = date_add(a.date, INTERVAL 3 DAY) )
AND id_student IN
(SELECT id_student FROM absences
WHERE id_student = a.id_student
AND date = date_add(a.date,INTERVAL 4 DAY) )
/* To avoid the case (Monday-Thursday-Friday) to be consider as consecutive days*/
AND WEEKDAY(date) !=0
Thanks
