Re: Counting # of consecutive rows with specified value(s)?

Thank you both for the suggestions. I started playing with the window
functions, but found and copied an "islands and gaps" example that didn't
need them, and was simpler than I thought. This query seems to do the
trick:

SELECT
client_id,
count(*)
FROM
(SELECT
client_id,
attendance_code
FROM recovery_circle_attendance rca
WHERE attended_on >
(SELECT max(attended_on)
FROM recovery_circle_attendance
WHERE client_id=rca.client_id AND attendance_code != 'ABSENT')
) foo
GROUP BY client_id;

It's a fairly small dataset, so at least right now I'm not too worried
about performance, but am curious if this is a reasonably well-optimized
way to get this info, or if there are any glaring issues or room for
improvement in this regard?

Cheers,
Ken

On Thu, Jun 7, 2012 at 12:35 PM, François Beausoleil
<francois(at)teksol(dot)info>wrote:

>
> Le 2012-06-06 à 22:20, Ken Tanzer a écrit :
>
>
> 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.
>
>
> This is similar to the islands and gaps problem. Search for that on
> StackOverflow and you'll get it.
>
> Bye!
> François
>

--
AGENCY Software
A data system that puts you in control
*http://agency-software.org/*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801