Find consecutive free numbers in table

Question

I have a table, containing numbers (phone numbers) and a code (free or not available).

Now, I need to find series, of 30 consecutive numbers, like 079xxx100 - 079xxx130, and all of them to have free status.

Here is an example how my table looks like:

CREATE TABLE numere
(
    value int,
    code varchar(10)
);


INSERT INTO numere (value,code)
Values
 (123100, 'free'),
 (123101, 'free'),
...
 (123107, 'booked'),
 (123108, 'free'),
 (...
 (123130, 'free'),
 (123131, 'free'),
 ...

 (123200, 'free'),
 (123201, 'free'),
...
 (123230, 'free'),
 (123231, 'free'),
 ...

I need a SQL query, to get me in this example, the 123200-123230 range (and all next available ranges).

Now, I found an example, doing more or less what I need:

select value, code
from numere
where value >= (select a.value
                from numere a
                left join numere b on a.value < b.value 
                                   and b.value < a.value + 30 
                                   and b.code = 'free'
                where a.code = 'free'
                group by a.value
                having count(b.value) + 1 = 30)
limit 30

but this is returning only the first 30 available numbers, and not within my range (0-30). (and takes 13 minutes to execute, hehe..)

If anyone has an idea, please let me know (I am using SQL Server)

score 2 · Accepted Answer · answered Apr 06 '16 at 14:01

2

This seems like it works in my dataset. Modify the select and see if it works with your table name.

DECLARE @numere TABLE
(
value int,
code varchar(10)
);


INSERT INTO @numere (value,code) SELECT 123100, 'free'

WHILE (SELECT COUNT(*) FROM @numere)<=30
BEGIN
    INSERT INTO @numere (value,code) SELECT MAX(value)+1, 'free' FROM @numere
END

UPDATE @numere
SET code='booked'
WHERE value=123105

select *
from @numere n1
inner join @numere n2 ON n1.value=n2.value-30
    AND n1.code='free'
    AND n2.code='free'
LEFT JOIN @numere n3 ON n3.value>=n1.value
    AND n3.value<=n2.value
    AND n3.code<>'free'
WHERE n3.value IS NULL

answered Apr 06 '16 at 14:01

UnhandledExcepSean

12,504
2
35
51

Thanks, this works fine, finding me 30 consecutive free numbers. I guess, finding from 00-30 range only, i will do it manually, because i will not have so many ranges avialable :). If someone have a quick way to find them from 00 ( i would think like a substr function, cuting the last digits, then check if they are within the 0-30 range. – Andrian Nani Apr 06 '16 at 14:25
Not understanding the latter part of your comment. Also, how was the performance compared to the method you originally used? – UnhandledExcepSean Apr 06 '16 at 14:31
Regarding peformance, i have tested now only in a dummy sqlite database, tomorrow i will check with production database. But it looks that is the same performance, more or less. Regarding my comment, this is a real use case, some company (with 30 employes), want to have all their numbers, being like 079xxxx00-079xxxx30, (the last 2 digits, from 0 to 30 ) and i need to select from the database, if there are free numbers, within this range. Anywhay, your answer is already extremley usseful as it is it is . Thanks – Andrian Nani Apr 06 '16 at 14:37
Oh! You could simply add "and n1.value like '079%00'" – UnhandledExcepSean Apr 06 '16 at 14:45

score 1 · Answer 2 · answered Apr 06 '16 at 14:08

This is usual Island and Gap problem.

; with cte as
( 
    select  *, grp  = row_number() over (order by value)
            - row_number() over (partition by code order by value)
    from    numere
),
grp as
(
    select  grp
    from    cte
    group by grp 
    having count(*) >= 30
)
select  c.grp, c.value, c.code
from    grp g
    inner join cte c    on  g.grp   = c.grp

score 1 · Answer 3 · answered Apr 06 '16 at 14:18

You can query table data for gaps between booked numbers using following SQL query where SQL LEAD() analytical function is used

;with cte as (
 select
  value, lead(value) over (order by value) nextValue
 from numere
 where code = 'booked'
), cte2 as (
select
 value gapstart, nextValue gapend,
 (nextValue - value - 1) [number count in gap] from cte
where value < nextValue - 1 
)
select * 
from cte2
where [number count in gap] >= 30

You can check the SQL tutorial Find Missing Numbers and Gaps in a Sequence using SQL

I hope it helps,

score 1 · Answer 4 · answered Apr 06 '16 at 16:03

Can't Test it at the moment, but this might work:

SELECT a.Value FROM (SELECT Value FROM numere WHERE Code='free' ) a INNER Join (SELECT Value FROM numere WHERE code='free' ) b ON b.Value BETWEEN a.Value+1 AND a.Value+29 GROUP BY a.Value HAVING COUNT(b.Value) >= 29 ORDER BY a.Value ASC

The output should be all numbers that have 29 free numbers following (so it's 30 consecutive numbers)

Thanks, this one works, displaying only the values, not also the codes ( i am very new to SQL, but i will try to modify, so i get in output both columns). — Andrian Nani, Apr 07 '16 at 05:53

Find consecutive free numbers in table

4 Answers4