SeriesInt
Updated: 17 August 2010
Use SeriesInt to generate a range of integer values.
Syntax
SELECT * FROM [Example].[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 Types
RETURNS TABLE (
[Seq] [int] NULL,
[SeriesValue] [int] NULL
)
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 a partial list of the resultant table:
Seq SeriesValue
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
Generate a series of numbers from 100 to 1 in decremements of 3.
SELECT * FROM wct.SeriesInt (100, 1, -3, NULL, 'L')
Here is a partial list of the resultant table:
Seq SeriesValue
----------- -----------
1 100
2 97
3 94
4 91
5 88
6 85
7 82
8 79
9 76
10 73
11 70
12 67
13 64
14 61
15 58
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.
Seq SeriesValue
----------- -----------
1 440
2 750
3 315
4 215
5 900
6 475
7 140
8 940
9 455
10 110
(10 row(s) affected)
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 a partial list of the resultant table:
Seq SeriesValue
----------- -----------
1 5
2 5
3 5
4 1
5 6
6 4
7 1
8 6
9 5
10 4
11 2
12 6
13 4
14 4
15 1
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.
D1 D2
----------- -----------
4 5
6 4
4 5
1 4
3 2
5 1
6 3
1 2
3 5
4 3
2 1
2 1
4 1
1 5
4 2
5 1
3 1
4 4
1 3
2 3
6 2
4 5
4 6
6 1
5 3
(25 row(s) affected)
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 1046
3 2000
4 2977
5 3996
6 5090
7 6045
8 4923
9 3979
10 2991
11 1950
12 1003
(11 row(s) affected)
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).
CHITEST
----------------------
0.875174792873638
(1 row(s) affected)
Alternatively, you could have used:
SELECT COUNT(*) as results
INTO #ar
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 D1 + D2
SELECT 1000 as expected into #er 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
SELECT wct.CHITEST2('#ar','results','',NULL,'#er','expected','',NULL)
This produced the following result. Your results will vary.
----------------------
0.734881876211591
(1 row(s) affected)