An Alternative Way to Group Ranges

In T-SQL Black Belt: “Grouping Ranges” (January 2005), Itzik Ben-Gan presents a puzzle that challenges readers to write a T-SQL query that generates a monthly report showing the activity periods (i.e., consecutive days open) of a small chain of stores. (These stores are located in US national parks. In bad weather, the storeowners don’t open the stores because few people visit the parks then.)

Ben-Gan uses the GROUP BY clause to solve the puzzle. However, there’s an alternative approach if you don’t want to use GROUP BY. This T-SQL query, which I wrote for SQL Server 2000, uses the NOT IN and TOP 1 clauses to identify the starting and ending points of each range of consecutive days.

—Tony Nitzke
[email protected]

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.