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

Tags: T-SQL