Find missing numbers in sequential int values SQL Server

Question

Suppose I have this table

BookNo. | page
001     |  01
001     |  02
001     |  04
002     |  01
002     |  03
003     |  01
003     |  02

Is it possible to get the booknos with missing page numbers?

Answer 1

This will work, except where page 1 is missing (written on oracle so syntax may be slightly different):

with cte1 as (
select distinct
   BookNo
  ,case when a.page + 1 != lead(page) over (partition by bookno order by page) then page + 1 end as missing_start
  ,case when a.page + 1 != lead(page) over (partition by bookno order by page) then lead(page) over (partition by bookno order by page) - 1 end as missing_end
from test a
where 1=1
)
select *
from cte1
where missing_start is not null
order by 1
;

Result:

| BOOKNO | MISSING_START | MISSING_END |
|--------|---------------|-------------|
| 001    | 3             | 3           |
| 002    | 2             | 2           |