Grouping Sequential Numbers with like attributes

[Joel Miller]

I found an article that was somewhat like what I was trying to do. The article was titled:
SQL Query - Find block of sequential numbers
Here is the article
http://bytes.com/topic/sql-server/an...ential-numbers

What I have is slightly different. I am trying to create a 'phone sheet' report which lists all the given phone numbers for a customer. Sometimes, the customer will have blocks of hundreds of sequential numbers that are all the same (except for the number itself). There are other fields like description, type, and LDCarrier. What I need is to list all of the phone numbers and additional info (description, type, LDCarrier) but group all of the numbers that are sequential and have the same description, type, and LDCarrier. If we have a phone sheet that lists all 2500 phone numbers, you can imagine how long listing all of those numbers would be... I would like to group all sequential numbers who have all the same type, description, and LDCarrier.

In one case, there is a group of 100 phone numbers (example - 3098699700 through 3098699799) which have all the same description, type, and LDCarrier. However, 3098699777 has a different 'type' value. I would need 3098699700 through 3098699776 grouped, 3098699777 all by itself and 3098699778 through 3098699799 all grouped.

Any numbers that are not sequential and the other fields are different would still be listed, but they would all be on separate lines instead of being grouped.

From the example query below (from the question I am referring to above), this query will group all sequential numbers, but it is not able to group numbers with other fields that have the same values. I am trying to model my query after that one and I am just not able to do it. Here is the query used in the other question:

SELECT *
FROM (SELECT first_consecutive = MIN(part_number), last_consecutive,
length = last_consecutive - MIN(part_number) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_number) AS last_consecutive
FROM PartNumbersAvailable AS P1
LEFT JOIN (SELECT P1.part_number
FROM PartNumbersAvailable AS P1
LEFT JOIN PartNumbersAvailable AS P2
ON P2.part_number = P1.part_number + 1
WHERE P2.part_number IS NULL) AS P2
ON P2.part_number >= P1.part_number
GROUP BY P1.part_number) AS P
GROUP BY last_consecutive) AS P
WHERE length >= 5
ORDER BY first_consecutive

Any help would be greatly appreciated!

Thanks!

[Joel Miller]

b0010100,
I think that is a lot closer. However, I need to show and group all of the fields type, description, carrier. Any time the phone numbers are consecutive and those other fields are the same, they would be grouped together.

I altered your code just slightly to show and group the other fields and altered the data creation only slightly to give a better indication of the types of things this needs to do. In this case, the only issue I see is as follows: You can execute the code I pasted and see the issue.

There is a block of 100 consecutive phone numbers (3098699700 through 3098699799) that all have (almost) exactly the same type, description, and carrier. Under normal circumstances, they would all be all grouped together as one record to display (3098699700 through 3098699799).

In this case, one of the numbers (3098699777) has a different carrier (CARRIER A). This one record would not be found within the range of consecutive, like numbers. It should split up that whole 100 block range into three.
One block of 3098699700 through 3098699776, one record for 3098699777, and one block for (3098699778 through 3098699799).

With the code I posted (which I hope I did not screw up any of the logic you intended), it did pull out 3098699777 but it did not put the other numbers into two separate blocks. Since that number is in the middle of the 100 block, those other 99 numbers are not CONSECUTIVE numbers any more, only 77 consecutive numbers, one all by itself, and another block of 22 consecutive numbers.

I hope this is not too confusing. thanks so much for all the help!

DECLARE @tbl TABLE(number Bigint,[type] char(10),[description] char(20),Carrier char(10)) 
DECLARE @t2 TABLE(number Bigint,[type] char(10),[description] char(20),nSet int,Carrier char(10)) 
DECLARE @c BIGINT 
DECLARE @nSet INT  
DECLARE @pNum BIGINT  
DECLARE @num bigint 
DECLARE @cName char(10) 
Declare @desc char(10) 
DECLARE @type char(10) 
 
SET @pNum = 0 
SET @nSet = 0 
 
SET @c = 3098699700 
WHILE @c <= 3098699776 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier B'); 
    SET @c = @c + 1 
END 
 
SET @c = 3098699777 
WHILE @c < =3098699777 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier A') 
    SET @c = @c + 1 
END 
 
SET @c = 3098299777 
WHILE @c  =3098299777 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier A') 
    SET @c = @c + 1 
END 
 
SET @c = 3098699778 
WHILE @c <=  3098699799 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier B') 
    SET @c = @c + 1 
END 
 
DECLARE cr CURSOR FOR 
SELECT * FROM @tbl ORDER BY [number] 
 
OPEN cr 
 
FETCH NEXT FROM cr 
INTO @num,@type,@desc, @cName 
 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 
    if NOT @num = @pNum + 1 
    BEGIN 
        print cast(@num as varchar(12)) + ' - ' + cast(@pnum as varchar(12)) 
        SET @nSet = @nSet + 1 
    END 
 
    INSERT INTO @t2 (number,[type],[description],nSet,Carrier) VALUES (@num,@type,@desc,@nSet,@cName) 
 
    SET @pNum = @num 
 
    FETCH NEXt FROM cr 
    INTO @num,@type,@desc, @cName 
END 
 
CLOSE cr 
DEALLOCATE cr 
 
  SELECT * FROM @t2 order by number
 
SELECT min(number)as min_num,max(number)as max_num,count(*) as Num_Count,Carrier, [type],[description]
FROM @t2 
GROUP BY nSet,[type],[description],Carrier, [type],[description]
order by min_num

[Jerry Winston]

Joel,

I think this is the final solution. The answer was right in front of us. What I did was add more criteria to the conditional statement that builds our number set(nSet).

    if NOT @num = @pNum + 1 
        OR NOT @cName = @pName
        OR NOT @type = @pType
        OR NOT @desc = @pDesc
 

Although we included type and description in the GROUP ON, they were given no consideration in the set builder! Any future fields added in the GROUP ON must also be added in the conditional statement as well.

DECLARE @tbl TABLE(number Bigint,[type] char(10),[description] char(20),Carrier char(10)) 
DECLARE @t2 TABLE(number Bigint,[type] char(10),[description] char(20),nSet int,Carrier char(10)) 
DECLARE @c BIGINT 
DECLARE @nSet INT  
DECLARE @pNum BIGINT  
DECLARE @pType char(10)
DECLARE @pDesc char(20)
DECLARE @pName char(10)
DECLARE @num bigint 
DECLARE @cName char(10) 
Declare @desc char(10) 
DECLARE @type char(10) 
 
SET @pNum = 0 
SET @pType = NULL
SET @pDesc = NULL
SET @pName = NULL
SET @nSet = 0 
 
SET @c = 3098699700 
WHILE @c <= 3098699776 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier B'); 
    SET @c = @c + 1 
END 
 
SET @c = 3098699777 
WHILE @c < =3098699777 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier A') 
    SET @c = @c + 1 
END 
 
SET @c = 3098299777 
WHILE @c  =3098299777 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier A') 
    SET @c = @c + 1 
END 
 
SET @c = 3098699778 
WHILE @c <=  3098699799 
BEGIN 
    INSERT INTO @tbl VALUES(@c,'East','My Descript','Carrier B') 
    SET @c = @c + 1 
END 
 
DECLARE cr CURSOR FOR 
SELECT * FROM @tbl ORDER BY [number] ASC 
 
OPEN cr 
 
FETCH NEXT FROM cr 
INTO @num,@type,@desc, @cName 
 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 
    if NOT @num = @pNum + 1 
        OR NOT @cName = @pName
        OR NOT @type = @pType
        OR NOT @desc = @pDesc
    BEGIN 
        print cast(@num as varchar(12)) + ' - ' + cast(@pNum as varchar(12)) 
        SET @nSet = @nSet + 1 
    END 
 
    INSERT INTO @t2 (number,[type],[description],nSet,Carrier) VALUES (@num,@type,@desc,@nSet,@cName) 
 
    SET @pNum = @num
    SET @pType = @type
    SET @pDesc=@desc
    SET @pName=@cName 
 
    FETCH NEXt FROM cr 
    INTO @num,@type,@desc, @cName 
END 
 
CLOSE cr 
DEALLOCATE cr 
 
  SELECT count(*) FROM @t2 group by number order by number
   SELECT * FROM @t2 
   --where Carrier = 'Carrier B '
   order by number
 
 
SELECT min(number)as min_num,max(number)as max_num,count(*) as Num_Count,Carrier, [type],[description]
FROM @t2 
GROUP BY nSet,Carrier,[type],[description]
order by min_num
 

Thanks for posting Joel! These questions help keep us SQL folks on our toes!

[Joel Miller]

Awesome! I think that is it. As it stands (as an example) it works perfectly and I understand it (That last part is kind of what I assumed but was not sure how to fix it). I will apply this logic to my real database as this was just a few fields as an example to make things easy. I will be able to tackle this in the morning and test away.

Thanks for everyone's help. I really appreciate it and I will test and get back to the thread tomorrow morning.

Thanks!

[ck9663]

Here's another approach without cursor and temp table....

POPULATE YOUR SAMPLE TABLE:

 
declare @phones as table (phone int, phonetype char(1), ld char(3), company char(3))
 
;WITH Number_Sequence
AS
(
   SELECT 900 AS sequence
   UNION ALL
   SELECT sequence+1
   FROM Number_Sequence W
      WHERE sequence < 1000
)
insert into @phones(phone, phonetype, ld, company)
SELECT sequence, 
   case
      when cast(sequence as char(3)) like '%7' then 'A'
      else 'B'
   end,
   case
      when cast(sequence as char(3)) like '%4' then 'Y'
      else 'Z'
   end,
   case
      when (sequence-900) / 30 <= 1 then 'ABC'
      when (sequence-900) / 30 = 2 then 'DEF'
      when (sequence-900) / 30 > 2 then 'GHI'
   end
FROM Number_Sequence
where sequence % 23 <> 0 and sequence % 13 <> 0 and sequence % 14 <> 0 
 
select company, phonetype, ld, phone from @phones 
order by company, phone, phonetype, ld
 
 

HERE'S THE CODE THAT WILL RETURN THE FIRST AND LAST OF THE TRUNK NUMBER SERIES....

;with phone1
as
(
select 
seq = row_number() over(partition by company, phonetype, ld order by phone),
company, phonetype, ld, phone
from @phones
),
firstphone
as 
(
   select 
   seq = row_number() over(order by p1.phone),
   p1.company, p1.phonetype, p1.ld, p1.phone
   from phone1 p1
      left join phone1 p2 on p1.company = p2.company and p1.phonetype = p2.phonetype and p1.ld = p2.ld and p1.seq = p2.seq + 1 
   where p2.phone is null or p1.phone - 1 <> p2.phone
) 
select 
p1.company, p1.phonetype, p1.ld, p1.phone as trunk_start, 
   trunk_end = 
      (
       select max(p3.phone) 
       from @phones p3
       where p1.company = p3.company and 
             p1.phonetype = p3.phonetype and
             p1.ld = p3.ld and      
             ((p2.phone is not null and p3.phone between p1.phone and p2.phone-1) or
              (p2.phone is null and p1.phone <= p3.phone)
             )
      )
from firstphone p1
   left join firstphone p2 on p1.seq = p2.seq - 1
order by p1.phone
 
 

The test data are pretty much spread. There are companies with just 2-3 numbers, then 1-2 gaps and there are companies with 4-5 numbers then a gap...

Happy Coding!!!

~~ CK

[NeoPa]

Very interesting CK. As you know I am trying to get back into T-SQL properly and this is very informative even if I haven't (yet) managed to see exactly what your SQL does (although I have a general grasp of the concepts you're using). I'm impressed by your approach, and very interested to see you use a version of 'WITH' that handles multiple CTEs. Something I've only recently learned about but have already fallen over not being able to define multiple ones.

So thanks for that. I will also look further into your solution as I've already found it to be innovative and informative. I hadn't even realised that ROW_NUMBER() was available before this either. How the posters in Access would love to see such information available to them, going by the number of questions we've had on that.

[NeoPa]

Would something like the following work ?

WITH ctePhone AS
(
SELECT ROW_NUMBER() OVER(Company
                       , PhoneType
                       , LD
                         ORDER BY Phone) AS SeqNo
     , Phone
     , Company
     , PhoneType
     , LD
FROM   @Phones
)
SELECT   MIN(Phone) AS First_Consecutive
       , CASE
         WHEN MIN(Phone) < MAX(Phone) THEN MAX(Phone)
         ELSE NULL
         END AS Last_Consecutive
       , MAX(SeqNo) - MIN(SeqNo) + 1 AS [Length]
       , Company
       , PhoneType
       , LD
FROM     ctePhone
GROUP BY Company
       , PhoneType
       , LD
ORDER BY MIN(Phone)

Obviously, I borrowed heavily from your work CK.

[Jerry Winston]

That's pretty CK.

[Joel Miller]

I was able to implement Jerry's solution into my code and it worked great! Thanks so much! I never would have gotten that done by myself and I learned a lot!

I have not been able to work on testing CK's or NEO's contributions but I am going to try that next.

Thanks everyone! This is one of the most helpful forums I have come across.

[NeoPa]

I've had some further thoughts on my suggestion and it appears there are situations where it would fall down. Specifically if the company etc details are the same for a bunch of records they will be treated as contiguous regardless of any breaks in the sequence.