Code to divide a column data into Range

DECLARE @NoOfIterations int, –Indicates in how many chunks you want to pull the data
@RangeAdd int,
@ColSIDStart int,
@ColSIDEnd int,
@Counter int ,
@MaxColSID int,
@MinColSID int

DECLARE @ColSIDRANGETBL TABLE
(
Iteration int,
ColSIDStart int,
ColSIDEnd int
)

SET @NoOfIterations = ?
SET @Counter = 1

SELECT @MinColSID = MIN(ColSID) FROM [dbo].[Table] (NOLOCK)
SELECT @MaxColSID = MAX(ColSID) FROM [dbo].[Table]] (NOLOCK)

SET @RangeAdd = (@MaxColSID – @MinColSID)/@NoOfIterations

SET @ColSIDStart = @MinColSID
SET @ColSIDEnd = @ColSIDStart + @RangeAdd

WHILE ( (@Counter<=@NoOfIterations) and (@ColSIDStart <= @ColSIDEnd) )
BEGIN
INSERT INTO @ColSIDRANGETBL
SELECT @Counter,@ColSIDStart,@ColSIDEnd

SET @ColSIDStart = @ColSIDEnd + 1
SET @ColSIDEnd = @ColSIDStart + @RangeAdd

IF(@ColSIDEnd>@MaxColSID)
BEGIN
SET @ColSIDEnd = @MaxColSID
END
SET @Counter = @Counter + 1
END

SELECT ColSIDStart, ColSIDEnd FROM @ColSIDRANGETBL

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s