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.
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.
