asked on # SQl Query to find x consecutive Nbrs in a Table

I have the need to query a table which has phone numbers Listed for consecutive nbrs.

My table hs numbers stored as int like

9255551000

9255551010

9255551011

9255551012

9255551013

9255551014

9255551015

9255551020

9255551021

9255551022

9255551023

9255551024

9255551025

I want to create an SP where i can say find for example block of 5 where the 5 is the number of consecutive nbrs i want

based on my sample data there should be 2 blocks and it should return the lowest nbr as the block start

Any idea how to achive that

My table hs numbers stored as int like

9255551000

9255551010

9255551011

9255551012

9255551013

9255551014

9255551015

9255551020

9255551021

9255551022

9255551023

9255551024

9255551025

I want to create an SP where i can say find for example block of 5 where the 5 is the number of consecutive nbrs i want

based on my sample data there should be 2 blocks and it should return the lowest nbr as the block start

Any idea how to achive that

Microsoft SQL ServerSQL

Could you add a brief summary of the purpose? Why do you need this?

A is your number column.

#temp should be your table.

Note that in the case of 5 you'll get

9255551010

9255551011

9255551020

9255551021

as 10 has a block of 5 and so does 11.

#temp should be your table.

```
declare @consec_Numbers int = 6
;with cte as
(select a, 0 b from
#temp
union all
select t1.a, b + 1
from #temp t1 join cte c on c.a + 1 = t1.a
where c.a != t1.a
)
select distinct a - @consec_Numbers + 1 final from cte
where b + 1 >= @consec_Numbers
```

Note that in the case of 5 you'll get

9255551010

9255551011

9255551020

9255551021

as 10 has a block of 5 and so does 11.

Pls try.. SET Based Approach..

**Table Creation.**

**Solution **

**Output...**

StartBlockNbrs EndBlockNbrs

9255551010 9255551015

9255551020 9255551025

```
--
CREATE TABLE nbr
(
nbrs BIGINT
)
GO
INSERT INTO nbr VALUES
(9255551000),
(9255551010),
(9255551011),
(9255551012),
(9255551013),
(9255551014),
(9255551015),
(9255551020),
(9255551021),
(9255551022),
(9255551023),
(9255551024),
(9255551025)
GO
--
```

```
--
DECLARE @NoOfConsecutiveBlocks AS INT = 5
SELECT MIN(nbrs) StartBlockNbrs , MAX(nbrs) EndBlockNbrs
FROM
(
SELECT nbrs , rnk , COUNT(rnk) OVER (PARTITION BY rnk) cnt
FROM
(
SELECT * , nbrs - ROW_NUMBER() OVER (ORDER BY nbrs) rnk FROM nbr
)u
)k
WHERE cnt >= @NoOfConsecutiveBlocks
GROUP BY rnk
--
```

StartBlockNbrs EndBlockNbrs

9255551010 9255551015

9255551020 9255551025

Curiosity overwhelms me ... are we building a telemarketing solution?

The "business rules" are not sufficiently clear in my view. If you want blocks of EXACTLY 5 consecutive numbers then there are zero blocks matching that description in the sample (0 to 5 is a set of 6 rows).

If you want blocks with AT LEAST 5 consecutive numbers then there are 2 blocks (6 rows in each)

If you want blocks with AT LEAST 5 consecutive numbers then there are 2 blocks (6 rows in each)

```
--
DECLARE @NoOfConsecutiveBlocks AS INT = 5
SELECT MIN(nbrs) StartBlockNbrs , MAX(nbrs) EndBlockNbrs
FROM
(
SELECT nbrs , rnk , COUNT(rnk) OVER (PARTITION BY rnk) cnt
FROM
(
SELECT * , nbrs - ROW_NUMBER() OVER (ORDER BY nbrs) rnk FROM nbr
)u
)k
WHERE cnt = @NoOfConsecutiveBlocks
GROUP BY rnk
--
```

```
--
DECLARE @NoOfConsecutiveBlocks AS INT = 5
SELECT MIN(nbrs) StartBlockNbrs , MAX(nbrs) EndBlockNbrs
FROM
(
SELECT nbrs , rnk , COUNT(rnk) OVER (PARTITION BY rnk) cnt
FROM
(
SELECT * , nbrs - ROW_NUMBER() OVER (ORDER BY nbrs) rnk FROM nbr
)u
)k
WHERE cnt >= @NoOfConsecutiveBlocks
GROUP BY rnk
--
```

Hi AlexPonnath,

Feedback will be appreciated for this.

Thank you !

Feedback will be appreciated for this.

Thank you !

Hi AlexPonnath,

Any luck with this?

Thank you !

Any luck with this?

Thank you !

Closing this via equal split. We have multiple solutions of the problem.

Pawan Kumar Khowal 250 Points

Kyle Abrahams 250 Points

Thank you.

Pawan Kumar Khowal 250 Points

Kyle Abrahams 250 Points

Thank you.

To be honest its fumy how someone awards himself 250 for a solution that does not work. Pawan's solution does not

produce the output as desired or advertised.

I have a table filled with numbers from 2000 - 2999 and if i ask for block of 100 i get the start and end as 2000 and 2999

produce the output as desired or advertised.

I have a table filled with numbers from 2000 - 2999 and if i ask for block of 100 i get the start and end as 2000 and 2999

```
DECLARE @NoOfConsecutiveBlocks AS INT = 1
SELECT MIN(PhoneNumber) StartBlockNbrs , MAX(PhoneNumber) EndBlockNbrs
FROM
(
SELECT PhoneNumber , rnk , COUNT(rnk) OVER (PARTITION BY rnk) cnt
FROM
(
SELECT * , PhoneNumber - ROW_NUMBER() OVER (ORDER BY PhoneNumber) rnk FROM [dbo].[tbl_NumberPool]
)u
)k
WHERE cnt >= @NoOfConsecutiveBlocks
GROUP BY rnk
```

>To be honest its fumy how someone awards himself 250 for a solution that does not work.

As you can imagine that decision last year to allow experts to include themselves in accepted answers was not without controversy..

As you can imagine that decision last year to allow experts to include themselves in accepted answers was not without controversy..

3 months later? Would have been helpful if you kept up with the question as well. Only way he was able to do it, lol. You can create an RA to remove that answer.

So again there is something wrong with Paul's code as it works with the limited data but in larger data set it fails.

I have the table filled with 1000 numbers from 2094902000 to 2094902999 and if i run the query i get the flowing result

StartBlockNbrs EndBlockNbrs

2094902000 2094902999

I have the table filled with 1000 numbers from 2094902000 to 2094902999 and if i run the query i get the flowing result

StartBlockNbrs EndBlockNbrs

2094902000 2094902999

```
DECLARE @NoOfConsecutiveBlocks AS INT = 5
SELECT MIN([PhoneNumber]) StartBlockNbrs , MAX([PhoneNumber]) EndBlockNbrs
FROM
(
SELECT [PhoneNumber] , rnk , COUNT(rnk) OVER (PARTITION BY rnk) cnt
FROM
(
SELECT * , [PhoneNumber] - ROW_NUMBER() OVER (ORDER BY [PhoneNumber]) rnk FROM [tbl_NumberPool]
)u
)k
WHERE cnt >= @NoOfConsecutiveBlocks
GROUP BY rnk
```

I'm going to post a solution that assumes that each of the consecutive blocks of phone numbers should begin with a **phone number ending with zero.** If I'm making a bad assumption, we can adjust things.

So, first we'll create our dataset.

Then we'll group things up. I tried to break down the moving parts into two CTEs in the hopes that the code would be more understandable.

The output will look something like this:

Something to consider is how ranges might overlap if a block size of more than 10 is chosen. Try running with a block size of 14 or something to see what I mean.

So, first we'll create our dataset.

```
if object_id('tempdb..#Nbrs') is not null drop table #Nbrs
--
;with cte_Nums as (
select cast(2094902000 as bigint) as Num
union all
select Num + 1
from cte_Nums n
where n.Num <= 2094902999
)
--
select *
into #Nbrs
from cte_Nums n
option (maxrecursion 10000)
```

Then we'll group things up. I tried to break down the moving parts into two CTEs in the hopes that the code would be more understandable.

```
--
declare @BlockSize int = 5
--
;with cte_SetAnchors as (
select der.Num, der.Consecutive, der.PossibleAnchor,
der.PossibleAnchor + @BlockSize as EndOfRange
from (
select n.Num,
-- is this row consecutive with the one before?
case when n.Num - lag(n.Num) over(order by n.Num) = 1 then 1 else 0 end as Consecutive,
-- "anchor numbers" always start with a zero as our least signif (right most) digit
case when n.Num % 10 = 0 then Num else null end as PossibleAnchor
from #Nbrs n
) der
)
--
,cte_FindRanges as (
select a.*,
(select sum(Consecutive) from cte_SetAnchors a2
where a2.Num > a.PossibleAnchor
and a2.Num <= a.EndOfRange) as NbrConsecutive
from cte_SetAnchors a
)
select f.PossibleAnchor as Anchor,
f.EndOfRange, f.NbrConsecutive as ConsecutivePhoneNumbers
from cte_FindRanges f
where f.NbrConsecutive = @BlockSize
```

The output will look something like this:

```
Anchor EndOfRange ConsecutivePhoneNumbers
2094902000 2094902005 5
2094902010 2094902015 5
2094902020 2094902025 5
2094902030 2094902035 5
2094902040 2094902045 5
2094902050 2094902055 5
2094902060 2094902065 5
...
```

Something to consider is how ranges might overlap if a block size of more than 10 is chosen. Try running with a block size of 14 or something to see what I mean.

Ok, here is a bit more info to my scenario, i have already a table which has the numbers stored in it. It stores all nbr ranges i have avail.

Also the numbers can start at any point as i would query only for nbrs which have previously not be assigned. My table has 2 relevant fields for this query one is the phonenumber the other is status as the status will indicate if the nbrs are still avail to be assigned. Once the user picks one of the ranges i update the table and mark those as assigned.

I tried your query code against my db and your temp result and there seems to be some issues.

a) for example if i go and uses as is it creates same output as you which skips a block as it always starts 10,20,30 and so on

but in case of 5 it should be 00,05,10,15,20 and so on. Also the End of range is wrong as 00,01,02,03,04 is 5 numbers so it should

look like

Also if i use a block of 100 it still breakes everything down same way as on your sample

Also the numbers can start at any point as i would query only for nbrs which have previously not be assigned. My table has 2 relevant fields for this query one is the phonenumber the other is status as the status will indicate if the nbrs are still avail to be assigned. Once the user picks one of the ranges i update the table and mark those as assigned.

I tried your query code against my db and your temp result and there seems to be some issues.

a) for example if i go and uses as is it creates same output as you which skips a block as it always starts 10,20,30 and so on

but in case of 5 it should be 00,05,10,15,20 and so on. Also the End of range is wrong as 00,01,02,03,04 is 5 numbers so it should

look like

```
Anchor EndOfRange ConsecutivePhoneNumbers
2094902000 2094902004 5
2094902005 2094902009 5
2094902010 2094902014 5
```

Also if i use a block of 100 it still breakes everything down same way as on your sample

```
2094902000 2094902100 100
2094902010 2094902110 100
2094902020 2094902120 100
2094902030 2094902130 100
```

To the overlap issue, the query should start looking for next avail which is bigger then the previous EndofRange which should eliminate that problem

So, you don't care which number is the starting number for a block? For example, if you've used 2097902001 and 20979020002, would our next block of five consecutive numbers start with 2097902003? Our group would be 2097902003 - 2097902007.

>>"*So again there is something wrong with Paul's code*"

I (Paul) have not offered code__to this point__. A long way back I did suggest that the requirements were not sufficiently clear and I still fear this may be the case.

What does an "the expected result" look like? You have mentioned that min and max alone are not sufficient. What is?

Also. Can you tell us which version of mssql you use. It does alter our choice of solution options. Apologies if already given its hard to see via a phone.

I (Paul) have not offered code

What does an "the expected result" look like? You have mentioned that min and max alone are not sufficient. What is?

Also. Can you tell us which version of mssql you use. It does alter our choice of solution options. Apologies if already given its hard to see via a phone.

Well as I have been accused of providing code, may as well actually do some.

Using this sample data:

Heer's another:

Using this sample data:

```
declare @consecs as int
set @consecs = 5
declare @Pnumbers table
([Pnumber] bigint, [Status] int)
;
INSERT INTO @Pnumbers
([Pnumber], [Status])
VALUES
(9255551000, 1),
(9255551010, 1),
(9255551011, 1),
(9255551012, 1),
(9255551013, 1),
(9255551014, 1),
(9255551015, 1),
(9255551020, 1),
(9255551021, 1),
(9255551022, 1),
(9255551023, 1),
(9255551024, 1),
(9255551025, 1)
```

And these 3 queries:
```
select
t.Pnumber AS starting_at
, t.Pnumber + ca.y AS ending_at
, ca.y AS Nums_available
, 'v1' as query
from @Pnumbers t
cross apply (
select count(*) y
from @Pnumbers x
where x.pnumber >= t.pnumber and x.pnumber <= (t.pnumber + @consecs)
and x.status = 1
) ca
where ca.y >= @consecs
order by t.Pnumber
select
t.Pnumber AS starting_at
, t.Pnumber + ca.y AS ending_at
, ca.y AS Nums_available
, 'v2' AS query
from @Pnumbers t
cross apply (
select count(*) y
from @Pnumbers x
where x.pnumber >= t.pnumber and x.pnumber <= (t.pnumber + @consecs)
and x.status = 1
) ca
where ca.y = @consecs
order by t.Pnumber
select
t.Pnumber AS starting_at
, t.Pnumber + ca.y AS ending_at
, ca.y AS Nums_available
, 'v3' AS query
from @Pnumbers t
cross apply (
select count(*) y
from @Pnumbers x
where x.pnumber <= t.pnumber and x.pnumber >= (t.pnumber - @consecs)
and x.status = 1
) ca
where ca.y = @consecs
order by t.Pnumber
```

I get these 3 different results:
```
+----+-------------+------------+----------------+-------+
| | starting_at | ending_at | Nums_available | query |
+----+-------------+------------+----------------+-------+
| 1 | 9255551010 | 9255551016 | 6 | v1 |
| 2 | 9255551011 | 9255551016 | 5 | v1 |
| 3 | 9255551020 | 9255551026 | 6 | v1 |
| 4 | 9255551021 | 9255551026 | 5 | v1 |
+----+-------------+------------+----------------+-------+
```

```
+----+-------------+------------+----------------+-------+
| | starting_at | ending_at | Nums_available | query |
+----+-------------+------------+----------------+-------+
| 1 | 9255551011 | 9255551016 | 5 | v2 |
| 2 | 9255551021 | 9255551026 | 5 | v2 |
+----+-------------+------------+----------------+-------+
```

```
+----+-------------+------------+----------------+-------+
| | starting_at | ending_at | Nums_available | query |
+----+-------------+------------+----------------+-------+
| 1 | 9255551014 | 9255551019 | 5 | v3 |
| 2 | 9255551024 | 9255551029 | 5 | v3 |
+----+-------------+------------+----------------+-------+
```

The sample data is derived from original question and subsequent comments, and the result output is assumed.Heer's another:

```
select
t.Pnumber AS starting_at
, case when ca.y >= @consecs then t.Pnumber + @consecs end AS ending_at
, ca.y AS Nums_available
, 'v4' as query
from @Pnumbers t
cross apply (
select count(*) y
from @Pnumbers x
where x.pnumber >= t.pnumber and x.pnumber <= (t.pnumber + @consecs)
and x.status = 1
) ca
where ca.y >= @consecs
order by t.Pnumber
+----+-------------+------------+----------------+-------+
| | starting_at | ending_at | Nums_available | query |
+----+-------------+------------+----------------+-------+
| 1 | 9255551010 | 9255551015 | 6 | v4 |
| 2 | 9255551011 | 9255551016 | 5 | v4 |
| 3 | 9255551020 | 9255551025 | 6 | v4 |
| 4 | 9255551021 | 9255551026 | 5 | v4 |
+----+-------------+------------+----------------+-------+
```

Ok using the below query it produces an output which seems to be wrong. If i look for 5 Nbr blocks why does it return 6

Output

Also the range it returns is not 6 but even 7 nbrs

```
DECLARE @consecs AS INT = 5
select
t.PhoneNumber AS starting_at
, t.PhoneNumber + ca.y AS ending_at
, ca.y AS Nums_available
, 'v1' as query
from tbl_NumberPool t
cross apply (
select count(*) y
from tbl_NumberPool x
where x.PhoneNumber >= t.PhoneNumber and x.PhoneNumber <= (t.PhoneNumber + @consecs)
and x.status = 1
) ca
where ca.y >= @consecs
order by t.PhoneNumber
```

Output

```
starting_at ending_at Nums_available query
2094902000 2094902006 6 v1
2094902001 2094902007 6 v1
2094902002 2094902008 6 v1
2094902003 2094902009 6 v1
2094902004 2094902010 6 v1
2094902005 2094902011 6 v1
2094902006 2094902012 6 v1
2094902007 2094902013 6 v1
2094902008 2094902014 6 v1
```

Also the range it returns is not 6 but even 7 nbrs

When i try the second query i get nothing what i would expect.

my database has for testing 1000 records and all have status of 1 with the Start of 2094902000 and end of 2094902999

running this query should have returned 10 rows but did only return 1

So not sure what causes the issue if its either the record count or any specific nbr combination

```
DECLARE @consecs AS INT = 100
select
t.PhoneNumber AS starting_at
, t.PhoneNumber + ca.y AS ending_at
, ca.y AS Nums_available
, 'v2' AS query
from tbl_NumberPool t
cross apply (
select count(*) y
from tbl_NumberPool x
where x.PhoneNumber >= t.PhoneNumber and x.PhoneNumber <= (t.PhoneNumber + @consecs)
and x.status = 1
) ca
where ca.y = @consecs
order by t.PhoneNumber
```

my database has for testing 1000 records and all have status of 1 with the Start of 2094902000 and end of 2094902999

running this query should have returned 10 rows but did only return 1

```
starting_at ending_at Nums_available query
2094902900 2094903000 100 v2
```

So not sure what causes the issue if its either the record count or any specific nbr combination

This is running on MSSQL 2014

How about trying the approach below? The performance isn't terrific, but you get to use window functions and recursion. :-) The code could be more concise, but I figured it was worth breaking things out to show the technique.

See my initial suggestion above to build the #Nbrs table.

See my initial suggestion above to build the #Nbrs table.

```
/**-----------------------------------------------------------------------------------------------------**/
/**-----------------------------------------------------------------------------------------------------**/
if object_id('tempdb..#PossibleBlocks') is not null drop table #PossibleBlocks
if object_id('tempdb..#Final') is not null drop table #Final
--
declare @BlockSize int = 8
-- identify our consecutive numbers using LAG()
;with cte_ConsecutiveNums as (
select n.Num,
-- is this row consecutive with the one before?
case when n.Num - lag(n.Num) over(order by n.Num) = 1 then 1 else 0 end as Consecutive,
row_number() over(order by n.Num) as [Row]
from #Nbrs n
)
-- determine which numbers can be "anchors" (the start of a block of numbers)
,cte_FindAnchors as (
select cn.Num, cn.Consecutive, cn.[Row],
case when (select sum(cn2.Consecutive) from cte_ConsecutiveNums cn2
where cn2.[Row] between cn.[Row] + 1 and cn.[Row] + (@BlockSize - 1)) = (@BlockSize - 1)
then 1
end as EnoughNumsForGroup
from cte_ConsecutiveNums cn
)
-- select both the beginning and end phone number values for all possible blocks
,cte_FindEndOfRange as (
select f.Num, f.Consecutive, f.[Row], f.EnoughNumsForGroup,
case when f.EnoughNumsForGroup = 1 then f.Num + (@BlockSize - 1) end as EndOfRange
from cte_FindAnchors f
)
select *
into #PossibleBlocks
from cte_FindEndOfRange f
-- we don't want overlapping blocks, so we'll identify "next anchor" values
select f.Num, f.Consecutive, f.EnoughNumsForGroup, f.EndOfRange,
(select min(f2.Num) from #PossibleBlocks f2 where f2.Num > f.EndOfRange
and f2.EndOfRange is not null) as NextAnchor
into #Final
from #PossibleBlocks f
-- recurse through our set to return all valid blocks
;with cte_Recurse as (
select f.*
from #Final as f
where f.Num = (select min(f2.Num) from #Final as f2 where f2.EnoughNumsForGroup = 1)
union all
select f.*
from #Final as f
join cte_Recurse r
on r.NextAnchor = f.Num
)
select r.Num, r.EndOfRange, (r.EndOfRange - r.Num) + 1 PhoneNumbers
from cte_Recurse r
option (maxrecursion 10000)
```

The second query is DELIBERATELY returning fewer rows due to the where clause. That is why I presented all results so you could choose the most promising option.

Did you try queries 1, 3 or 4?

[+ edit]*NONE of my proposed options ABOVE removes overlaps, so if this is an absolute requirement then ignore all my suggestions 1,2,3 & 4*

Regrettably you have not shared much test data and I'm still in the dark about your output requirement.

Did you try queries 1, 3 or 4?

[+ edit]

Regrettably you have not shared much test data and I'm still in the dark about your output requirement.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

If you get the chance, please try my second suggestion from above. I think it matches all of your requirements.

```
/**-----------------------------------------------------------------------------------------------------**/
/**-----------------------------------------------------------------------------------------------------**/
if object_id('tempdb..#PossibleBlocks') is not null drop table #PossibleBlocks
if object_id('tempdb..#Final') is not null drop table #Final
--
declare @BlockSize int = 8
-- identify our consecutive numbers using LAG()
;with cte_ConsecutiveNums as (
select n.Num,
-- is this row consecutive with the one before?
case when n.Num - lag(n.Num) over(order by n.Num) = 1 then 1 else 0 end as Consecutive,
row_number() over(order by n.Num) as [Row]
from #Nbrs n
)
-- determine which numbers can be "anchors" (the start of a block of numbers)
,cte_FindAnchors as (
select cn.Num, cn.Consecutive, cn.[Row],
case when (select sum(cn2.Consecutive) from cte_ConsecutiveNums cn2
where cn2.[Row] between cn.[Row] + 1 and cn.[Row] + (@BlockSize - 1)) = (@BlockSize - 1)
then 1
end as EnoughNumsForGroup
from cte_ConsecutiveNums cn
)
-- select both the beginning and end phone number values for all possible blocks
,cte_FindEndOfRange as (
select f.Num, f.Consecutive, f.[Row], f.EnoughNumsForGroup,
case when f.EnoughNumsForGroup = 1 then f.Num + (@BlockSize - 1) end as EndOfRange
from cte_FindAnchors f
)
select *
into #PossibleBlocks
from cte_FindEndOfRange f
-- we don't want overlapping blocks, so we'll identify "next anchor" values
select f.Num, f.Consecutive, f.EnoughNumsForGroup, f.EndOfRange,
(select min(f2.Num) from #PossibleBlocks f2 where f2.Num > f.EndOfRange
and f2.EndOfRange is not null) as NextAnchor
into #Final
from #PossibleBlocks f
-- recurse through our set to return all valid blocks
;with cte_Recurse as (
select f.*
from #Final as f
where f.Num = (select min(f2.Num) from #Final as f2 where f2.EnoughNumsForGroup = 1)
union all
select f.*
from #Final as f
join cte_Recurse r
on r.NextAnchor = f.Num
)
select r.Num, r.EndOfRange, (r.EndOfRange - r.Num) + 1 PhoneNumbers
from cte_Recurse r
option (maxrecursion 10000)
```

Why was this re-opened? I received no notice.