Mark Smith

SQL Server 2005 - Missing Ranges

I recently had a situation where I needed to highlight the ids in a given range which were out of sequence and not part of a range (i.e. 1,2,3,4,6, would show that 5 was missing). Fortunately, I found that this was quite easy to do with Common Table Expressions (CTE) in SQL Server 2005.

Firstly, to demonstrate this functionality, I'll create a table variable and populate it with some sample data. I can then use my CTE (along with an OVER clause to add a row number) to join the table to itself and highlight the "start" and "end" of the missing rows.

Here's the example I came up with for SQL Server 2005:

DECLARE @Temp TABLE (IDName int)

INSERT INTO @Temp VALUES(1);
INSERT INTO @Temp VALUES(2);
INSERT INTO @Temp VALUES(3);
INSERT INTO @Temp VALUES(6);
INSERT INTO @Temp VALUES(7);
INSERT INTO @Temp VALUES(9);
INSERT INTO @Temp VALUES(11);
INSERT INTO @Temp VALUES(12);
INSERT INTO @Temp VALUES(13);

WITH rangesCTE(rowNum, IDName) AS 
(
SELECT ROW_NUMBER() OVER(ORDER BY IDName)
AS RowNum, IDName FROM @Temp
)
SELECT a.IDName+1 AS MissingStart,
b.IDName-1 AS MissingEnd
FROM rangesCTE a INNER JOIN rangesCTE b
ON a.RowNum = b.RowNum - 1
WHERE a.IDName-(b.IDName-1) < 0

Which will then generate the following output:

I'd be interested to see if anyone has any other alternative methods to do this for both SQL Server 2005 and SQL Server 2000