SQL Server SeriesInt Function

Updated 2023-10-18 20:12:42.147000

Description

Use the table-valued function SeriesInt to generate a range of integer values.

Syntax

SELECT * FROM [westclintech].[wct].[SeriesInt] (
  <@StartValue, float,>
 ,<@StopValue, float,>
 ,<@StepValue, float,>
 ,<@MaxIterations, float,>
 ,<@SeriesType, nvarchar(4000),>)

Arguments

@StartValue

the lower (or upper) limit of the integer range. The first value may be the maximum or the minimum value, depending on the @StepValue. @StartValue must be of the type float or of a type that implicitly converts to float. Non-integer values are truncated to integer.

@StopValue

the upper (or lower) limit of the integer range. The last value may be the maximum or the minimum value, depending on the @StepValue. @StopValue must be of the type float or of a type that implicitly converts to float. Non-integer values are truncated to integer.

@StepValue

the increment (or decrement) used to determine the next value in a linear series (see @SeriesType). In a random series, this is the multiple used to calculate a random number between the @StartValue and the @StopValue. @StepValue must be of the type float or of a type that implicitly converts to float. Non-integer values are truncated to integer.

@MaxIterations

the number of results to return. For linear series (see @SeriesType), a value of NULL will return all the values between the @StartValue and the @StopValue specified by the @StepValue. For random series, NULL is not permitted. @MaxIterations must be of the type float or of a type that implicitly converts to float. Non-integer values are truncated to integer.

@SeriesType

the letter 'L' or 'l' for linear series, or the letter 'R' or 'r' for random series. A linear series will generate numbers from the @StartValue incremented by the @StepValue until either the number of iterations is equal to @MaxIterations or the greatest value less than or equal to the @StopValue has been reached.

A random series will randomly generate number that are multiples of @StepValue until @MaxIterations is reached.

Return Type

table

Remarks

To generate a series for non-integer values use the SeriesFloat function.

To generate a series for dates use the SeriesDate function.

If the @StepValue > @StopValue and @SeriesType = 'L', then only one row will be returned with SeriesValue equal to @StartValue.

If the @StepValue > @StopValue and @SeriesType = 'R', then @MaxIterations rows will be returned with SeriesValue equal to the lesser of @StartValue and @StopValue.

If @SeriesType is NULL, then @SeriesType is set to 'L'.

If @StartValue is NULL, it will be calculated from @StopValue, @MaxIterations and @StepValue.

If @StopValue is NULL, it will be calculated from @StartValue, @MaxIterations and @StepValue.

If @StepValue is NULL and @SeriesType is 'L' and @StopValue > @StartValue then @StepValue is set to 1.

If @StepValue is NULL and @SeriesType is 'L' and @StopValue < @StartValue then @StepValue is set to -1.

If @SeriesType is 'L' and SIGN(@StepValue) <> SIGN(@StopValue - @StartValue) then only one row will be returned.

If @SeriesType is 'R', @MaxIterations must be greater than 0 and not equal to NULL.

If @SeriesType is 'R', @StartValue cannot be NULL

If @SeriesType is 'R', @StopValue cannot be NULL.

Examples

Generate a list of numbers from 1 to 100.

SELECT *
FROM wct.SeriesInt(1, 100, 1, NULL, 'L');

Here is the resultant table.

Generate a series of numbers from 100 to 1 in decrements of 3.

SELECT *
FROM wct.SeriesInt(100, 1, -3, NULL, 'L');

Here is the resultant table.

Generate 10 random numbers, in multiples of 5, between 1 and 1000. Your results will vary.

SELECT *
FROM wct.SeriesInt(1, 1000, 5, 10, 'R');

This produces the following results.

You can simulate 25 rolls of a die with the following statement. Your results will vary.

SELECT *
FROM wct.SeriesInt(1, 6, '', 25, 'R');

Here's the resultant table:

Here's a simulation of 25 throws of a pair of dice. Your results will vary.

SELECT n.D1,
       seriesvalue as D2
FROM
(SELECT seq, seriesvalue as D1 FROM wct.SeriesInt(1, 6, '', 25, 'R') ) n ,
wct.SeriesInt(1, 6, '', 25, 'R') m
WHERE m.seq = n.seq;

Which produces the following results.

Here's the results of a simulation of 36,000 throws of a pair of dice. Your results will vary.

SELECT D1 + D2,
       COUNT(*)
FROM
(
    SELECT n.D1,
           seriesvalue as D2
    FROM
    (
        SELECT seq,
               seriesvalue as D1
        FROM wct.SeriesInt(1, 6, '', 36000, 'R')
    ) n ,
    wct.SeriesInt(1, 6, '', 36000, 'R') m
    WHERE m.seq = n.seq
) o
GROUP BY D1 + D2
ORDER BY 1;

This produced the following result.

----------- -----------
          2         969
          3        2057
          4        3085
          5        4075
          6        5026
          7        6091
          8        4850
          9        4032
         10        2966
         11        1911
         12         938

You can combine SeriesInt with other functions to perform statistical analysis. The following query uses the SeriesInt function in the calculation of the chi-squared test for 36,000 rolls of the dice.

SELECT wct.CHITEST2_q('
SELECT COUNT(*)
FROM (SELECT n.D1
,seriesvalue as D2
FROM (SELECT seq
,seriesvalue as D1
FROM wct.SeriesInt(1, 6,' + wct.QUOTES('') + ',36000,' + wct.QUOTES('R') + ')) n,
wct.SeriesInt(1, 6,' + wct.QUOTES('') + ',36000,' + wct.QUOTES('R') + ') m
WHERE m.seq = n.seq) o
GROUP BY D1 + D2
ORDER BY D1 + D2',
'SELECT 1000 UNION ALL
SELECT 2000 UNION ALL
SELECT 3000 UNION ALL
SELECT 4000 UNION ALL
SELECT 5000 UNION ALL
SELECT 6000 UNION ALL
SELECT 5000 UNION ALL
SELECT 4000 UNION ALL
SELECT 3000 UNION ALL
SELECT 2000 UNION ALL
SELECT 1000') as CHITEST;

This produced the following result (your results will vary).