Mar 16, 2012

Sql query to find out missing numbers in a sequence in sql server


In sql sever by using sql query without creating any temp table we can find out missing sequence of integers from a table.

Suppose we have a table tblIndex like this :

CREATE TABLE tblIndex(
    ntIndex INTEGER
)

INSERT tblIndex VALUES(1),(3),(8)

This table has following data:

ntIndex
1
3
8

Now we want to find out missing numbers from sequence of integers from 1 to 8 i.e. output something like this:

ntIndex
2
4
5
6
7
  
Solution:

SELECT ntIndex AS MissingIndex
FROM udfSequenceTable(1,8)

EXCEPT

SELECT ntIndex FROM tblIndex

For this first we have to create the table valued function udfSequenceTable which is as follow:

CREATE FUNCTION udfSequenceTable(
    @MinRange INTEGER = 0,
    @MaxRange INTEGER
)
RETURNS @SequenceTable TABLE(
    ntIndex INTEGER
)
AS
BEGIN

    ;WITH cteSequenceTable
    AS(
         SELECT ntIndex
         FROM(SELECT @MinRange AS ntIndex) ST

         UNION ALL

         SELECT ntIndex + 1
         FROM(SELECT 1 AS ntTemp) ST
         INNER JOIN cteSequenceTable ON  1 =
         WHERE ntIndex  < @MaxRange
    )
    INSERT INTO @SequenceTable
    SELECT ntIndex FROM cteSequenceTable
    OPTION (MAXRECURSION 32767)

    RETURN
END

2 comments:

  1. This is a good code, I try it in SQLSERVER 2005, worked well.
    But how to do this code in visual basic 2005?
    I appreciate those of you who have been willing to help me to translate this code is to be done in vb.net 2005, thank you

    ReplyDelete
    Replies
    1. like as it is how to find out in sql?

      Delete