I'm working with an attendance table, where each person gets a record for each day of class, with an attendance code (ABSENT, ATTENDED, ...). I'm trying to figure out how to get the number of consecutive absences a person has. I'm guessing this can be done without writing a function, but I need some help here.
I can currently test whether someone has at least a specified number of consecutive absences with the query below, but it would be better to get the actual number.
As a second question, what about getting the number of consecutive records for a set of values? (e.g., attendance_code IN ('ATTENDED','EXCUSED')
Any ideas or suggestions? Thanks.
Ken
This query checks for 4 consecutive absences:
SELECT client_id,
array(
SELECT attendance_code
FROM attendance
WHERE client_id=enrollment.client_id
ORDER BY attended_on DESC
LIMIT 4
)=array_fill('ABSENT'::varchar,array[4]) AS absent_last_4
FROM enrollment;
AGENCY Software
A data system that puts you in control
(253) 245-3801
