Find the missing numbers in a given range

Question

Hi I have a table that has start and end columns that has the values

Start   End
9       10
11      19
29      44
45      51
56      60

Now if I give the range like 15 to 60 I need to return the values that not being covered in the above range.

The output I am expecting is - 20,21,22,23,24,25,26,27,28,52,53,54,55

How can I retrieve this?

Answer 1

In postgresql:

CREATE TABLE ranges
(n1 int,
n2 int);

INSERT INTO ranges
VALUES (9,10),(11,19),(29,44),(45,51),(56,60);

SELECT n FROM ranges
RIGHT JOIN generate_series(15,60) n
ON (n1 <= n AND n <= n2) WHERE n1 IS NULL;

Answer 2

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

SELECT * 
FROM StartEnd (Your original table)
RIGHT JOIN (SELECT ROWNUMBER() as num 
            FROM SomeTable (any table with 60 rows) 
            WHERE SomeTable.column1 <= 60) 
    as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
WHERE StartEnd.numbers IS NULL

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

EDIT: I thought you meant one column, not two, my bad.

Answer 3

This a solution for MS SQL Server 2008 and above

As alluded to by @Zane, this is best achieved using a numbers table, which for this exercise we'll create and populate with 100 numbers. If you already have your own Numbers table, feel free to substitute it in the final query,

if object_id('tempdb..#nums') is not null drop table #nums;
create table #nums ([num] int);
with digits (d) as (
    select 1 union select 2 union select 3 union
    select 4 union select 5 union select 6 union
    select 7 union select 8 union select 9 union
    select 0)
insert #nums (num)
select n.number
from (select i.d + ii.d * 10 as number from digits i cross  join digits ii) as n
where  n.number > 0
order by n.number;

This creates and populates a table with the ranges specified in your original post

if object_id('tempdb..#ranges') is not null drop table #ranges;
create table #ranges ([start] int, [end] int);
insert into #ranges ([start], [end])
values(9,10),(11,19),(29,44),(45,51),(56,60);

Now we just use an EXCEPT query to show the missing numbers. The first part of the query just lists the number from 15 to 60. The second part joins the numbers and ranges tables to list all the numbers in the specified ranges. The EXCEPT clause does the heavy lifting to provide the list

select n.[num] 
from #nums n -- my number table
where n.[num] between 15 and 60
except
select n.[num] 
from #nums n 
    inner join #ranges r -- my ranges table
    on n.[num] between r.[start] and r.[end]

For more info on the EXCEPT clause see this MSDN article

Cheers Phil