Find missing numbers in sequential int values SQL Server

Home / Uncategorized / 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:
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 |
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *