Consecutive absences in MySQL

Question

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   

At the moment I get sth like that:

3000000 2010-05-10  Monday  2010-05-11  Tuesday         2010-05-12  Wednesday 
3000000 2010-05-11  Tuesday 2010-05-12  Wednesday     2010-05-13    Thursday 
3000000 2010-05-07  Friday  2010-05-10  Monday          2010-05-11  Tuesday 
3000001 2010-05-14  Friday  2010-05-17  Monday          2010-05-18  Tuesday

But it shows the list of consecutive days break in groups of 3 days. I would like to get sth more similar to this:

3000000 2010-05-10  Monday  2010-05-11  Tuesday         2010-05-12  Wednesday  2010-05-13   Thursday 
3000000 2010-05-07  Friday  2010-05-10  Monday          2010-05-11  Tuesday 
3000001 2010-05-14  Friday  2010-05-17  Monday          2010-05-18  Tuesday

Thanks

Answer 1

This isn't the complete solution, and this query is untested, but it demonstrates how to get the first record in a series of three or more absences using self-joins:

SELECT a.id_student, a.date, DAYNAME(a.date) FROM absences a
JOIN absences a2
ON a2.id_student = a.id_student AND (a2.date = date_add(a.date, INTERVAL 1 DAY) OR (DAYNAME(a.date) = 'Friday' AND a2.date = date_add(a.date, INTERVAL 3 DAY)))
JOIN absences a3
ON a3.id_student = a.id_student AND (a3.date = date_add(a2.date, INTERVAL 1 DAY) OR
(DAYNAME(a3.date) = 'Friday' AND a3.date = date_add(a2.date, INTERVAL 3 DAY)))
LEFT JOIN absences a4
ON a4.id_student = a.id_student AND (a4.date = date_add(a.date, INTERVAL -1 DAY) OR
(DAYNAME(a4.date) = 'Monday' AND a4.date = date_add(a.date, INTERVAL -3 DAY)))
WHERE a4.id_student IS NULL

You use joins to get the sequence, and use a left join to only get the records that don't have an absence the day before to get the first record.