SQL Loop to build Case based on Variables-CodePudding

I am trying to build a case query based on variables The idea being is when the variables are populated the case statement would alter accordingly.

My Current query takes Values from a table and groups them together into a sort of Bucket. This works fine providing its always going to be the set ranges and number of ranges, I want to make this configurable by passing variables From my original query all i wanted was to configure the Number of Buckets and the value of From and Two for each bucket i.e. 5 or 10

Here is my original query:

SELECT subq.Bucket, COUNT(*) 'Count'
FROM
(
SELECT
  CASE
    WHEN R.Value < 10 THEN '0-10'
    WHEN R.Value Between 10 and 20 THEN '10-20'
    WHEN R.Value Between 20 and 30 THEN '20-30'
    WHEN R.Value Between 30 and 40 THEN '30-40'
    WHEN R.Value > 40  THEN '40 '
  END Bucket
FROM Table R
Where DateTime Between '2022-10-01' and '2022-11-10' and Type = 1
) subq

GROUP BY subq.Bucket

This is what i was trying to achomplish if it makes any sense in the realm of SQL

DECLARE @NoRows Int, @Range Int, @Count Int, @StartRange Int
Set @NoRows = 5
Set @StartRange = 0 
Set @Range = 10
Set @Count = 0

SELECT subq.Bucket, COUNT(*) 'Count'
FROM
(

WHILE @NoRows <= @Count
BEGIN
    SELECT
    (
    CASE
        WHEN R.Value Between @StartRange and @Range THEN '@StartRange-@Range' 

    SET @Count = @Count   1
    SET @StartRange = @StartRange   @Range 
END 
        WHEN R.Value > @StartRange  THEN '@StartRange'
    END Bucket
FROM Table R
Where DateTime Between '2022-10-01' and '2022-11-10' and Type = 1
) subq
GROUP BY subq.Bucket

CodePudding user response：

This is untested, due to no sample data, but this should be enough to get you to where you need to be. I use an inline tally here to generate the data, but you could also use a tally function, or even build you own bucket function:

DECLARE @NoRows int = 5,
        @Range int = 10,
        @StartRange int = 0;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP(@NoRows) 
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2), --UP to 100 rows, add more cross joins for more rows
Buckets AS(
    SELECT @StartRange   ((I-1)*@Range) AS RangeStart,
           @StartRange   ((I)*@Range) AS RangeEnd
    FROM Tally)
SELECT YT.{Needed Columns},
       CASE WHEN B.RangeStart IS NULL THEN CONCAT(@NoRows * @Range,' ')
            ELSE CONCAT(B.RangeStart,'-', B.RangeEnd-1)
       END AS Bucket
FROM dbo.YourTable YT
     LEFT JOIN Buckets B ON YT.YourColumn >= B.RangeStart
                        AND YT.YourColumn < B.RangeEnd;

In SQL Server 2022 , you even have the built in function GENERATE_SERIES, which makes this even easier.