Geek Answers Handbook

Find consecutive toll free numbers in a table

I have a table containing numbers (phone numbers) and a code (free or inaccessible).

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

Here is an example of what 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 an SQL query to get the range 123200-123230 (and all of the following available ranges) in this example.

Now I have 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 only returns the first 30 available numbers, and not within my range (0-30). (and takes 13 minutes to complete, hehe ..)

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

+4
4

, . , .

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
+2

.

; 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
+1

, SQL-, SQL LEAD()

;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

SQL SQL

, ,

+1

, :

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

, 29 ( 30 )

+1

More articles:


All Articles