• 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