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
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801
