CODEWITHSUNDEEP
sqlsql-server
Lajith
Lajith

Reputation: 1867

Split a range of numbers in equal parts SQL Sever

I have following query to split the range of numbers in parts. For ex: i have range of 200 numbers and i have to group the numbers to 95 But Result is coming in reverse order.I Have attached expected result

declare @MinValue bigint = 1 
declare @MaxValue bigint = 200;

declare @RowsPerGroup bigint =95
declare @RowsPerGroup1 bigint =(@RowsPerGroup-1)
;with src(val,rm) as (
select @MaxValue, (@MaxValue - @RowsPerGroup1) union all
select rm-1, case when rm-1 > @MinValue + @RowsPerGroup1 then rm-1 - @RowsPerGroup1 else @MinValue end from src where rm-1 >= @MinValue
)
select rm as 'Start', val  as 'End',[Difference]=(val-rm)+1 from src order by rm asc
option(maxrecursion 0)

Current Result:

Start   End Difference
1       10  10
11     105  95
106    200  95

Expected Result:

enter image description here

Pls let me know where am doing wrong

Upvotes: 1

Views: 719

Answers (2)

outis
outis

Reputation: 77400

You can see the reason in the first line of the common table expression:

select @MaxValue, (@MaxValue - @RowsPerGroup1)

This inserts (200,106) intto src. The second select then counts down from existing rows. To adapt the CTE, exchange minimums with maximums (including for ranges), invert arithmetic, reverse comparisons and any other related exchanges:

    select @MinValue, (@MinValue + @RowsPerGroup1) union all
    select val+1, case
        when val+1 < @MaxValue - @RowsPerGroup1
          then val+1 + @RowsPerGroup1
        else @MaxValue
        end
      from src
      where val+1 <= @MaxValue

This particular statement can be simplified in parts:

    select @MinValue, (@MinValue + @RowsPerGroup - 1) union all
    select val + 1, case
        when val + @RowsPerGroup < @MaxValue
          then val + @RowsPerGroup
        else @MaxValue
        end
      from src
      where val < @MaxValue

Upvotes: 1

Sergey Menshov
Sergey Menshov

Reputation: 3906

My variant:

DECLARE
  @MinValue bigint = 1,
  @MaxValue bigint = 200,
  @RowsPerGroup bigint = 95 -- 300

;WITH cte AS(
  SELECT @MinValue [Start],IIF(@RowsPerGroup>@MaxValue,@MaxValue,@RowsPerGroup) [End]

  UNION ALL

  SELECT [End]+1,IIF([End]+@RowsPerGroup>@MaxValue,@MaxValue,[End]+@RowsPerGroup)
  FROM cte
  WHERE [End]<@MaxValue
)
SELECT [Start],[End],[End]-[Start]+1 [Difference]
FROM cte

Upvotes: 4

Related Questions