Re: Finding consecutive dates when dates are not truly consecutive

If you are going to do this a lot, you can create a special calendar
table and put all of the temporal data you need into it.

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
..
absence_2 DATETIME NOT NULL,
absence_3 DATETIME NOT NULL,
CHECK (cal_date < absence_2 AND absence_2 < absence_3));

This will let you check for weekends, long holidays, summer vacations,
etc. I am making an assumption that if someone is out of school for
two days before the end of the term and then is absence the first day
of the next term, this is not counted as three consecutive days. But
if he misses Thursday, Friday and Monday in a term, he is counted.

SELECT student_id
FROM Calendar AS C, StudentAbsences AS S1
WHERE S1.absence_date IN (cal_date, absence_2, absence_3)
GROUP BY student_id
HAVING COUNT(*) = 3;

Loading the Calendar table is pretty quick and easy. You can use a
spread*** which will have a lot of calendar functions built into it.
Load the three columns with (cal-date, cal_date+1, cal_date+2), then do
updates for weekends and holidays.

You will find Calendar handy for other things. A calendar table for US
Secular holidays can be built from the data at this website:
http://www.smart.net/~mmontes/ushols.html

.