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

This page was last updated on May 01, 2006 04:28 PM.