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 <franc...@teksol.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.tan...@agency-software.org (253) 245-3801