Bucketing consecutive numbers in a range

Recently we had a little tricky problem to solve. We were displaying a report in which we had to bucket numbers in a range, such that only consecutive numbers should be in that range, if any break is there, then a new range should start. Our first solution did not worked as required. Most difficult part was identifying numbers in a sequence, and placing them in a bucket. We could not create any simple T-SQL queries which could easily sort these things out. Then we thought of first capturing the bucket of each number so that we can easily work it out, and that was not possible without cursors. Lets have a look how we did that.

First we will create a table for our scenario.

-- creating a test table
CREATE TABLE Phones
(
    SWITCH  VARCHAR(50),
    NPA     CHAR(3),
    NXX     CHAR(3),
    Number  CHAR(4),
    Bucket  CHAR(4)
)

Then we need to populate our table with some data.

-- populating sample data
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2234', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2235', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2236', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2237', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2238', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2240', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2241', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2242', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2243', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2244', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2250', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2251', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2252', NULL)
INSERT INTO Phones VALUES ('SWITCH1', '444', '555', '2253', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '444', '555', '2254', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '444', '555', '2255', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '444', '555', '2256', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '444', '555', '2257', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '666', '888', '1002', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '666', '888', '1003', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '666', '888', '1004', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '666', '888', '1005', NULL)
INSERT INTO Phones VALUES ('SWITCH2', '666', '888', '1006', NULL)

Here is the main bucketing logic.

-- captur sequential range and assign bucket
DECLARE @Switch      VARCHAR(50),
        @NPA         CHAR(3),
        @NXX         CHAR(3),
        @Number      CHAR(4),
        @PrevNumber  CHAR(4),
        @Bucket      INT

SET @PrevNumber = ''

DECLARE SwitchCursor  CURSOR  
FOR
    SELECT DISTINCT
           SWITCH,
           NPA,
           NXX        
    FROM   Phones

OPEN SwitchCursor

FETCH NEXT FROM SwitchCursor INTO @Switch, @NPA, @NXX                      

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE NumberCursor CURSOR  
    FOR
        SELECT Number
        FROM   Phones
        WHERE  SWITCH = @Switch
               AND NPA = @NPA
               AND NXX = @NXX
        ORDER BY
               CAST(Number AS INT)
    
    OPEN NumberCursor
    
    FETCH NEXT FROM NumberCursor INTO @Number
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @PrevNumber = ''
            SET @Bucket = @Number
        ELSE 
        IF CAST((CAST(@Number AS INT) - 1) AS CHAR(4)) = @PrevNumber
            SET @Bucket = @Bucket
        ELSE
            SET @Bucket = @Number
        
        SET @PrevNumber = @Number
        
        UPDATE Phones
        SET    Bucket = @Bucket
        WHERE  SWITCH = @Switch
               AND NPA = @NPA
               AND NXX = @NXX
               AND Number = @Number
        
        FETCH NEXT FROM NumberCursor INTO @Number
    END
    
    CLOSE NumberCursor
    DEALLOCATE NumberCursor
    
    FETCH NEXT FROM SwitchCursor INTO @Switch, @NPA, @NXX
END

CLOSE SwitchCursor
DEALLOCATE SwitchCursor

Now lets see the results with following query

-- display proper ranges and their count
SELECT SWITCH,
       NPA,
       NXX,
       MIN(CAST(Number AS INT)) AS FirstNumber,
       MAX(CAST(Number AS INT)) AS LastNumber,
       COUNT(*) AS Numbers
FROM   Phones
GROUP BY
       SWITCH,
       NPA,
       NXX,
       Bucket

Ok, results looks like what was required, clean things out.

DROP TABLE Phones

4 thoughts on “Bucketing consecutive numbers in a range

  1. Ghulam Mustafa

    Just reading very old stuff at your pages. I believe you already have better script for this range related stuff, but I’m writing it here for you. No need to run cursors for this

    ; With cte
    as
    (select SWITCH, NPA, NXX, Number
    , Bucket = Number – ROW_NUMBER() over (partition by SWITCH, NPA, NXX order by Number)
    from phones
    )
    select SWITCH, NPA, NXX, min(Number) FirstNumber, max(Number) LastNumber, Count(*) as Numbers
    from cte
    group by SWITCH, NPA, NXX, Bucket

    Reply
    1. Zafar Iqbal Post author

      Thanks for the suggestions. Indeed it was an old script, which I used for a report. Common table expression were not supported by client’s SQL Server, so devised this and got the desired results.

      Those were my best days with TSQL, spent nearly 4 years creating queries for reports, data processing, and business logic. Always something new coming at me.

      Reply
  2. Ghulam Mustafa

    Forget to mention that no need to have Bucket column in your table. You may drop it (if not required by some other process)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.