dynamically split a numeric range using T-SQL

[747576]

Hi,
The sql below creates a temporary table that populates numeric ranges, basically I can define a numeric range i.e. 0 to 200 and the number of splits i.e. 4, the query then splits the range into 4 intervals.

The split column is my split id, so I know which is the 1st, 2nd, 3rd interval and so on.
The problem is the identity column is populating in reverse so the first interval 0 to 50 is Split = 4 (would like it to be split = 1)

The question is how can I modify the sql to reverse ths split column or have the identity populate in reverse.

Thank you

DECLARE @splitinterval TABLE (
	[split] [int] identity(1, 1) NOT NULL
	,[start] FLOAT
	,[end] FLOAT
	)
DECLARE @start FLOAT
	,@end FLOAT
	,@split INT
	,@interval FLOAT

SET @start = 0
SET @end = 200
SET @split = 4
SET @interval = @end / @split

DECLARE @inc INT

SET @inc = 1

INSERT INTO @splitinterval
SELECT @interval * (@split - @inc)
	,@interval * @split

WHILE (@inc < @split)
BEGIN
	BEGIN
		INSERT INTO @splitinterval
		SELECT (@interval * (@split - @inc)) - @interval
			,@interval * (@split - @inc)

		SET @inc = @inc + 1
	END

	IF (@inc > @split)
		BREAK
	ELSE
		CONTINUE
END

SELECT *
FROM @splitinterval
ORDER BY split asc

 

[gmmastros]

It looks like you are making this more complicated than it has to be. Try this instead:

DECLARE @splitinterval TABLE (
	[split] [int] identity(1, 1) NOT NULL
	,[start] FLOAT
	,[end] FLOAT
	)
DECLARE @start FLOAT
	,@end FLOAT
	,@split INT
	,@interval FLOAT

SET @start = 0
SET @end = 200
SET @split = 4
SET @interval = @end / @split

While @start < @end
  Begin
    Insert Into @splitinterval(Start, [End])
    Select @Start, @Start + @Interval
		
    Set @Start = @Start + @Interval
  End

Select * From @splitinterval

You can simplify this by just incrementing the @start variable by the interval amount.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom