SQL summary of missing numbers in sequence

Question

I would like to find gaps in a sequence and summarize the findings in the following way:

number sequence: 2, 3, 4, 8, 9, 12, 13, 14, 15
missing numbers: 0, 1, 5, 6, 7, 10, 11
min number: 0 (always)
max number: max number of the sequence (15 in this example)

The summary should look like:

From | To | # of missing  
00   | 01 | 2  
05   | 07 | 3  
10   | 11 | 2

I am using SQL server and in reality, the sequence will contain many more numbers (close to a million). I have found many scripts that find and list the missing numbers in the sequence, but I can't figure out how to summarize it in the desired way.

If it helps, the field is called BELNR and the table is called BSEG.

EDIT: with the help from the Gaps and Islands material, I have been able to find a solution (may not be the most optimal one, but I think it works):

with C as
(
select belnr, row_number() over(order by belnr) as rownum
from bseg
)
select cast(Cur.belnr as bigint) + 1 as [From], cast(nxt.belnr as bigint) - 1 as [To], (cast(nxt.belnr as bigint) - 1) - (cast(Cur.belnr as bigint) + 1) + 1  as [# of Missing]
from C as Cur
join C as Nxt
    on Nxt.rownum = cast(Cur.rownum as int) +1
Where cast(nxt.belnr as bigint) - cast(Cur.belnr as bigint) > 1

your output looks incorrect. from and To have values from missing numbers — ughai, May 05 '15 at 08:56
You say "_I have found many scripts that find and list the missing numbers in the sequence_" ...please include that information and what it produces with the above dataset, this will show that you have a partial solution and you want to format the output into a summary. Otherwise, you're relying on users to create 2 solutions for you, when you already have the first part. — Tanner, May 05 '15 at 09:03
@ughai: I want to show the Gaps (missing values) Tanner: I have posted the code I used above — janez_007, May 05 '15 at 09:19
my point exactly. you are missing values 5,6 and 7 however you have a row `05 | 07 | 3`. how did you decide to get 5 and 7 and exclude out 6 from your output — ughai, May 05 '15 at 09:22
it was meant as a range - from 5 to 7, which includes the numbers 5, 6, 7 (thus the number of missing is 3). In real world examples I am missing ranges with 10k or more missing numbers, so it would make no sense to list them all. Sorry for the missunderstanding — janez_007, May 05 '15 at 09:28

score 0 · Accepted Answer · answered May 05 '15 at 08:29

This is called Islands and Gaps problem. Read more here:

https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

The word ‘Gaps’ in the title refers to gaps in sequences of values. Islands are unbroken sequences delimited by gaps. The ‘Gaps and Islands’ problem is that of using SQL to rapidly detect the unbroken sequences, and the extent of the gaps between them in a column.

SQL summary of missing numbers in sequence

1 Answers1