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.
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 firstname.lastname@example.org. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.