MISSING SERIES

Question

Hi all, Im creating a stored procedure/function in MS SQL which should return the missing series.

Example:

the table "orders" contain field name "ordNo".

ordNo

000001

000003

000005

the functions should return these values:

000002

000004

any idea?

thank you very much.

Answer 1

the pseudo code for it goes like this:

1.start at 00001

2.Increment by 1 and check if this exists in the table (ordNo).

3.If not exists, return the number, else repeat the process.

Answer 2

What about something simple like:

SELECT ordNo - 1
FROM Orders o
WHERE NOT EXISTS (
    SELECT *
    FROM Orders n
    WHERE n.ordNo = o.OrdNo - 1        
)
AND ordNo > 1

Edit: Ah - this won't find missing "runs" in the series though. Only single missing numbers.

Here's a version which I think will at least find the "from" and "to" values for "missing" order numbers:

SELECT (SELECT MAX(ordNo) + 1 FROM Orders m WHERE m.ordNo < o.OrdNo) fromOrdNo,
    (ordNo - 1) toOrdNo
FROM Orders o
WHERE NOT EXISTS (
    SELECT *
    FROM Orders n
    WHERE n.ordNo = o.OrdNo - 1        
)
AND ordNo > 1

Answer 3

Perhaps the following example should help.

-- the table that will have rows numbered from 1 to 1000
select top 1000 identity(int,1,1) as id into #X from syscolumns

-- the table with missing values
select top 200 identity(int,1,2) as id into #Y from syscolumns

-- select * from #x
-- select * from #y

select #x.id, #y.id from #x
left outer join #y on #x.id = #y.id
where  #y.id is null

You should have a temp table like #x, which will have all the values (including max value of the row). In above example, I am assuming the range is from 1 to 1000.