Clever Geek Handbook

MISSING SERIES

Im creating a stored procedure / function in MS SQL that should return the missing series.

Example:

the orders table contains the field name "ordNo".

ordNo

000001

000003

000005

functions should return these values:

000002

000004

any idea?

Thank you very much.

0


source to share


3 answers


How about something simple:

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

Edit: A - this won't find any missing "runs" in the series. Single missing numbers only.



Here's a version that I think will at least find the "from" and "to" values ​​for the "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
+1


source


the pseudocode for it looks like this:

1.start at 00001



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

3.If does not exist, return the number, otherwise repeat the process.

0


source


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 temporary table of type #x that will have all values ​​(including the maximum row value). In the example above, I am assuming the range is 1 to 1000.

0


source






More articles:


All Articles