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

[Ken Tanzer <ken.tanzer@xxxxxxxxx>]

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@xxxxxxxxxxx> 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.tanzer@xxxxxxxxxxxxxxxxxxx

(253) 245-3801