5

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)

Improve this question

4 Answers 4

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
Not understanding the latter part of your comment. Also, how was the performance compared to the method you originally used?
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
Oh! You could simply add "and n1.value like '079%00'"
1

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
Improve this answer

Comments

1

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,

Improve this answer

Comments

1

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)

Improve this answer

1 Comment

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).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.