I'd been pointed at Itzik's work to solve a problem I'd set myself so a copy of the book would be good.

Here's the code I used to deal with the first half of the problem.

create table #Orders (OrderID int)

insert into #Orders values

(111101),

(111102),

(111103),

(111105),

(111106),

(111110),

(111111),

(111112),

(111113),

(111119),

(111125)

;

with cte as(

select

row1 = ROW_NUMBER() over(order by sl.OrderID desc)

,sl.OrderID

from #Orders sl

group by sl.OrderID

)

select

Row = ROW_NUMBER() over(order by o1.row1)

,diff = o2.OrderID - o1.OrderID

,o1.OrderID

into #missing

from cte o1

join cte o2 on o1.row1 = (o2.row1+1)

where

(o2.OrderID - o1.OrderID) > 1

select * from #missing

drop table #missing,#Orders

Edit Spelling