Database Scripts

Beta
 Log In    |   Sign Up

SQL Server : Find Gaps in A Range Using CTEs

Added on Feb-27-2012 by leon_sql
For SQL Server

Tags : range

Common Table Expressions (CTE's) introduced in SQL Server 2005 are useful in performing recursive operations. This scripts demonstrates how CTEs can be used to find the gaps in a range.

Versions

SQL Server 2005 +

DOWNLOAD

DECLARE @i INT
SELECT @i = MAX(col1) FROM nums;
WITH tmp (GapCol1) AS
(
        SELECT DISTINCT a.col1 + 1
        FROM nums a
        WHERE NOT EXISTS
                (SELECT 1 FROM nums b
                WHERE b.col1 = a.col1 + 1)
        AND a.col1 < @i
        UNION ALL
        SELECT a.GapCol1 + 1
        FROM tmp a
        WHERE NOT EXISTS
            (SELECT 1
            FROM nums b
            WHERE b.col1 = a.GapCol1 + 1)
        AND a.GapCol1 < @i
    )
SELECT GapCol1
FROM tmp
ORDER BY GapCol1;


    

Report Script

blog comments powered by Disqus