SQl Query to find x consecutive Nbrs in a Table

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

A is your number column.
#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 

 
 
 

Select allOpen in new window

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.

--

CREATE TABLE nbr
(
	nbrs BIGINT
)
GO

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

Select allOpen in new window

Solution

--


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


--

Select allOpen in new window

Output...


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)

So Update for my solution - If you need exactly 5 consecutive the you should use below. Also agree with Paul sir on - if you need exactly 5 then we dont have any record in your sample

WHERE cnt  = @NoOfConsecutiveBlocks

--


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


--

Select allOpen in new window

If you need 5 consecutive or more then you should use below.

WHERE cnt  >= @NoOfConsecutiveBlocks

--


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


--

Select allOpen in new window

Hi AlexPonnath,

Feedback will be appreciated for this.

Thank you !

Hi AlexPonnath,

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.

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

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

Select allOpen in new window

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

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

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

Select allOpen in new window

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.

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) 

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

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

Select allOpen in new window

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

Select allOpen in new window

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.

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

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)
    

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

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

Select allOpen in new window

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

Select allOpen in new window

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.

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

Select allOpen in new window

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

Select allOpen in new window

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.

/**-----------------------------------------------------------------------------------------------------**/
/**-----------------------------------------------------------------------------------------------------**/
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) 

Select allOpen in new window

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.

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) 

Select allOpen in new window

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