Inside Microsoft® SQL Server® 2008: T-SQL Querying

Missing and Existing Ranges (Also Known as Gaps and Islands)

To put your knowledge of subqueries, table expressions, and ranking calculations into action, I’ll provide a couple of problems that have many applications in production environments. I’ll present a generic form of the problem, though, so you can focus on the techniques and not the data.

The problems at hand deal with a sequence of values that has gaps within it. The sequence can be numeric (for example, keys such as order IDs) or temporal (for example, order dates). Also, the sequence can have unique values (for example, keys), or it can have duplicate values (for example, order dates). The first challenge is to identify the ranges of missing values in the sequence (gaps), and the second ...