Zafar Iqbal

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