Binaryworld - Finding gaps in sequential numbers. ... [ SQL Server -> T-SQL ]

A simple JOIN that will determine gaps in a set of sequential values. This query will basically give the sequence number after which a gap is present.

Click here to copy the following block

CREATE TABLE #t ( seq int identity );
WHILE( SELECT COALESCE( MAX( seq ) , 0 ) FROM #t ) < 18
INSERT #t DEFAULT VALUES
DELETE #t WHERE seq%3 = 0
DELETE #t WHERE seq = 11
go
SELECT * FROM #t
-- Sample data:
/*
seq
-----------
     1
     2
     4
     5
     7
     8
     10
     13
     14
     16
     17
*/

SELECT a.seq AS GapAfterSeq
FROM #t a
WHERE NOT EXISTS( SELECT * FROM #t b
         WHERE b.seq = a.seq + 1 ) and
   a.seq < ( SELECT MAX( seq ) FROM #t )
/*
GapAfterSeq
-----------
     2
     5
     8
     10
     14
*/

-- Setup more sample data for boundary condition for test below:
DELETE #t WHERE seq = 1
SELECT * FROM #t
/*
seq
-----------
     2
     4
     5
     7
     8
     10
     13
     14
     16
     17
*/
GO

/*
    This is a very generic form of query that can be used to
    determine a gap given a range. The above query cannot
    determine if a value is not present & is less than the existing
    minimum value. Hence, this form of the query can handle that
    boundary conditions too.
*/
DECLARE @rangemin int , @rangemax int
SELECT @rangemin = 1 , @rangemax = 20

SELECT MIN( seq ) + 1 AS NextSeq
FROM (
    SELECT @rangemin - 1 AS seq
    WHERE NOT EXISTS( SELECT * FROM #t WHERE seq = @rangemin )
    UNION ALL
    SELECT a.seq
    FROM #t a
    WHERE NOT EXISTS( SELECT * FROM #t b
             WHERE b.seq = a.seq + 1 And
                b.seq BETWEEN @rangemin And @rangemax ) And
       a.seq >= @rangemin And a.seq < @rangemax
) AS t

/*
NextSeq
-----------
     1
*/
GO
DROP TABLE #t;
GO