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!

[ck9663]

When you say..

I would need 3098699700 through 3098699776 grouped, 3098699777 all by itself and 3098699778 through 3098699799 all grouped.

Do you mean you just need an identifier that group them all together? Or you want it to be in a single row?

~~ CK

[Joel Miller]

Thanks for helping.

What the example does for me (and that part works) is that it gives me a row for each group, starting with first_consecuti ve and then last consecutive and then the length.

In the example given it would return the following:
first_consecuti ve Last consecutive length type
3098699700 3098699776 77 ThisType
3098699777 3098699777 01 ThatType
3098699778 3098699799 22 ThisType

I hope this helps.

Thanks!

[ck9663]

What would happen if there's a gap on the sequence but they have the same type?

~~ CK

[Jerry Winston]

This is an interesting requirement, I'll see if I can help.

[Joel Miller]

That would be a new record. Only sequential phone numbers with exactly the same type, description, and LDCarrier would be grouped.

Thanks!

[Joel Miller]

b0010100,
That does not group sequential numbers at all. That only groups numbers with the same carrier. I will sometimes have thousands of numbers and I need to group any sequential numbers that have the same LDCarrier, type and description together.

If I had the number 9999999999 in that table with a Carrier of 'Carrier B' you code would have added that to the middle record and there would have been two numbers with Carrier B.

Thanks!

[Jerry Winston]

Joel,

You're 100% right, I jumped the gun on my solution. I though I pulled it back in time but I guess I didn't. Here's something that may work better for you:

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 A');
    SET @c = @c + 1
END
 
SET @c = 3098699777
WHILE @c < =3098699777
BEGIN
    INSERT INTO @tbl VALUES(@c,'West','My Descript','Carrier B')
    SET @c = @c + 1
END
 
SET @c = 3098299777
WHILE @c < =3098299777
BEGIN
    INSERT INTO @tbl VALUES(@c,'South','My Descript','Carrier B')
    SET @c = @c + 1
END
 
SET @c = 3098699778
WHILE @c <=  3098699799
BEGIN
    INSERT INTO @tbl VALUES(@c,'North','My Descript','Carrier C')
    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 min(number),max(number),count(*),Carrier
FROM @t2
GROUP BY nSet,[type],[description],Carrier

[ck9663]

What's the maximum number, in sequence, that a company have? Thousands, meaning the last 4 digits are the only unique portion? Ten thousands, meaning the last 5 digits are the only unique portion?

~~ CK

[Joel Miller]

ck9663,
I am not sure what your question is exactly but I think you are asking what is the maximum a company could have. this is for 10 digit phone numbers so theoretically it could be numbers between 0000000000 and 9999999999 (Although I don't think those numbers exist or those area codes exist.). However, I do need to accomodate for any numbers within that range. The are any 10 digit numbers. They could have a range of 300 consecutive numbers, 10, 500 non-consecutive numbers, etc... Anyone can pick any numbers they want. Although if we can, we try to use consecutive blocks of numbers for them to keep things simple.

We want to list all of their phone numbers and associated information about those numbers, but in an effort to keep the list as short as possible, I would like to group together any consecutive numbers with the same values.

Thanks!