Checking sequence with SQL query.

I have a table orders that keeps all order from all our stores. I wrote a query to check the sequence orders for each store. It looks like that.

select WebStoreID, min(webordernumber), max(webordernumber), count(webordernumber) 
from orders
where ordertype = 'WEB' 
group by WebStoreID

I can check it all orders are present with this query. web ordernumber is number from 1...n.

How can I write query to find missing orders without joining to temporary/different table?

From stackoverflow

• I would make an auxiliary table of "all integers from 1 to n" (see http://www.sql-server-helper.com/functions/integer-table.aspx for some ways to make it with a SQL Server function, but since it's something you will need over and over I'd make it into a real table anyway, and with any SQL engine it's easy to make that, just once) then use a nested query, SELECT value FROM integers WHERE value NOT IN (SELECT webordernumber FROM orders) &c. Also see http://www.sqlmag.com/Article/ArticleID/99797/sql_server_99797.html for a problem similar to yours, "detecting gaps in a sequence of numbers".

  THEn : + Thanks for great links.

• If your database supports analytic functions then you could use a query something like:

  select prev+1, curr-1 from
  ( select webordernumber curr,
           coalesce (lag(webordernumber) over (order by webordernumber), 0) prev
    from   orders
  )
  where prev != curr-1;
  

  The output will show the gaps e.g.

  prev+1 curr-1
  ------ ------
       3      7
  

  would mean that numbers 3 to 7 inclusive are missing.

  THEn : Thanks. What is nvl? I cannot find it on MSSQL ?

  Tony Andrews : Sorry, I wrote my answer under the misapprehension that it was an Oracle question - NVL is Oracle speak for COALESCE. I have updated my answer to use COALESCE (which if you don't have that is equivalent to CASE WHEN param1 IS NOT NULL THEN param1 ELSE param2 END)

• You could join the table on itself to detect rows which have no previous row:

  select cur.*
  from orders cur
  left join orders prev 
      on cur.webordernumber = prev.webordernumber + 1
      and cur.webstoreid = prev.webstoreid
  where cur.webordernumber <> 1
  and prev.webordernumer is null
  

  This would detect gaps in the 1...n sequence, but it would not detect duplicates.

  Jeff O : May want to show: SELECT cur.webordernumber + 1 AS Missing_OrderNumber, cur.* from ... For a sequence of: 101, 102, 105 - only 103 would show up missing. Not sure if it is important to display 104 as well.