Archive for June 2013

SQL for Breaks in a Sequence

In a previous post, Views You Can Use: Ordinal Numbers, we discussed how to acknowledge children who visited Pee Wee Bees most often.  The little boy who had been here the most (88 times to date) should be in first place.  The little girl who has visited 83 times should be in second place.  It’s easy to count each child’s number of visits; harder to assign ordinal numbers (first, second, third) and recognize ties (two siblings have been here 79 times).

I also have a “Hot Streaks” list that shows the children who have visited Pee Wee Bees at least once per calendar month for at least the past three calendar months.  This was a more challenging query.  A child who visits once in January, once in February, and once in each following month throughout 2012, would have a streak of twelve consecutive months.  A child who visited ten times in September, ten times in October, not once in November, and ten times in December would have a “streak” of one month.

Continue reading ‘SQL for Breaks in a Sequence’ »