Atif Sheikh (4/29/2010)
Hi...
DECLARE @MissingNumbers TABLE (N INT)
Declare @vMax int
INSERT INTO @MissingNumbers
VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20)
Set @vMax = (Select MAX(N) from @MissingNumbers)
;with wcte as (
Select Top(@vMax) ROW_NUMBER() over(order by a.N) NAll
from @MissingNumbers a, @MissingNumbers b
) Select NAll from wcte a
Left outer Join @MissingNumbers b on b.N = a.Nall
where b.N is Null
Atif SHeikh
Sorry, but this does not work when the number of missing numbers is more then 50%. For example
DECLARE @MissingNumbers TABLE (N INT)
Declare @vMax int
INSERT INTO @MissingNumbers
VALUES (19),(20)
Set @vMax = (Select MAX(N) from @MissingNumbers)
;with wcte as (
Select Top(@vMax) ROW_NUMBER() over(order by a.N) NAll
from @MissingNumbers a, @MissingNumbers b
) Select NAll from wcte a
Left outer Join @MissingNumbers b on b.N = a.Nall
where b.N is Null
will result in
1
2
3
4