SQL Server SeriesFloat Function

Updated 2023-10-18 19:59:18.197000

Description

Use the table-valued function SeriesFloat to generate a range of floating point values.

Syntax

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

Arguments

@StartValue

the lower (or upper) limit of the floating point 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.

@StopValue

the upper (or lower) limit of the floating point 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.

@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.

@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 numbers that are multiples of @StepValue until @MaxIterations is reached.

Return Type

table

colName	colDatatype	colDesc
Seq	int	A monotonically increasing sequence number
SeriesValue	float	The float variable

Remarks

To generate a series for integer values use the SeriesInt function.

To generate a series for date values 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 10.

SELECT *
FROM wct.SeriesFloat(0.1, 10.0, 0.1, NULL, 'L');

This produces the following result.

Seq	SeriesValue
1	0.1
2	0.2
3	0.3
4	0.4
5	0.5
6	0.6
7	0.7
8	0.8
9	0.9
10	1
11	1.1
12	1.2
13	1.3
14	1.4
15	1.5
16	1.6
17	1.7
18	1.8
19	1.9
20	2
21	2.1
22	2.2
23	2.3
24	2.4
25	2.5
26	2.6
27	2.7
28	2.8
29	2.9
30	3
31	3.1
32	3.2
33	3.3
34	3.4
35	3.5
36	3.6
37	3.7
38	3.8
39	3.9
40	4
41	4.1
42	4.2
43	4.3
44	4.4
45	4.5
46	4.6
47	4.7
48	4.8
49	4.9
50	5
51	5.1
52	5.2
53	5.3
54	5.4
55	5.5
56	5.6
57	5.7
58	5.8
59	5.9
60	6
61	6.1
62	6.2
63	6.3
64	6.4
65	6.5
66	6.6
67	6.7
68	6.8
69	6.9
70	7
71	7.1
72	7.2
73	7.3
74	7.4
75	7.5
76	7.6
77	7.7
78	7.8
79	7.9
80	8
81	8.1
82	8.2
83	8.3
84	8.4
85	8.5
86	8.6
87	8.7
88	8.8
89	8.9
90	9
91	9.1
92	9.2
93	9.3
94	9.4
95	9.5
96	9.6
97	9.7
98	9.8
99	9.9
100	10

Generate a series of numbers from 10 to -10 in decrements of 3.3333.

SELECT *
FROM wct.SeriesFloat(10, -10, -3.3333, NULL, 'L');

Here is the resultant table:

Seq	SeriesValue
1	10
2	6.6667
3	3.3334
4	9.99999999997669E-05
5	-3.3332
6	-6.6665
7	-9.9998

Generate 10 random numbers, between 0 and 1. Your results will vary.

SELECT *
FROM wct.SeriesFloat(0, 1, '', 10, 'R');

This produces the following results (your results will be different).

Seq	SeriesValue
1	0.0787300244340347
2	0.0393758332540169
3	0.237071212957181
4	0.275765785144533
5	0.765305606073377
6	0.506849329688982
7	0.654947900052624
8	0.0535740512672691
9	0.774494760099097
10	0.73788640263392

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

SELECT *
FROM wct.SeriesFloat(1, 100, .03125, 10, 'R');

This produces the following results.

Seq	SeriesValue
1	85.625
2	58.59375
3	96.28125
4	97.6875
5	87.3125
6	70.90625
7	42.46875
8	98.9375
9	63.40625
10	78.875

You can use the SeriesFloat function in conjunction with other functions to generate random numbers consistent with known statistical distributions. For example, we could randomly generate 15 numbers from the standard normal distribution with the following statement:

SELECT seq,
       wct.NORMSINV(seriesvalue) as NORMSINV
FROM wct.SeriesFloat(0, 1, '', 15, 'R');

This produces the following result:

seq	NORMSINV
1	0.115115246564977
2	0.0861232660335375
3	-0.51211327862208
4	0.0833024753960621
5	-1.1040088954424
6	0.665494336529001
7	-0.980701775124289
8	0.445878666345121
9	0.300888521636836
10	-0.197957795305958
11	-0.0368872432918503
12	-1.81611011290773
13	0.397721052691346
14	-0.0960160763401366
15	0.8706113574682

We could generate 15 random numbers for a normal distribution with a mean of 100 and a standard deviation of 15 with the following statement:

SELECT seq
,wct.NORMINV(seriesvalue, 100, 15)
FROM wct.SeriesFloat(0,1,'',15,'R');

This produces the following result.

seq
1	106.529474006228
2	123.00857181537
3	80.099070876261
4	98.0276399646496
5	91.7737036409259
6	108.09737139573
7	85.050164912141
8	122.951511599054
9	115.077771376198
10	93.4072825124728
11	86.2396638843069
12	92.7253168560602
13	103.122155353833
14	91.1914297396313
15	103.55078537015

We could generate 15 random numbers for a gamma distribution with a shape parameter of 9 with the following statement:

SELECT seq
,wct.GAMMAINV(seriesvalue, 9, 1)
FROM wct.SeriesFloat(0,1,'',15,'R');

This produces the following result.

seq
1	13.5629377694821
2	11.1932994911787
3	10.9438339532567
4	9.90162997348385
5	14.6773466847694
6	10.3188014158663
7	10.7589410774399
8	10.637936085491
9	10.1797678083148
10	8.82692712547266
11	10.700105146452
12	10.4919966511707
13	5.94455182109008
14	10.9149500274106
15	8.01495898641042

Seq	SeriesValue
1	0.1
2	0.2
3	0.3
4	0.4
5	0.5
6	0.6
7	0.7
8	0.8
9	0.9
10	1
11	1.1
12	1.2
13	1.3
14	1.4
15	1.5
16	1.6
17	1.7
18	1.8
19	1.9
20	2
21	2.1
22	2.2
23	2.3
24	2.4
25	2.5
26	2.6
27	2.7
28	2.8
29	2.9
30	3
31	3.1
32	3.2
33	3.3
34	3.4
35	3.5
36	3.6
37	3.7
38	3.8
39	3.9
40	4
41	4.1
42	4.2
43	4.3
44	4.4
45	4.5
46	4.6
47	4.7
48	4.8
49	4.9
50	5
51	5.1
52	5.2
53	5.3
54	5.4
55	5.5
56	5.6
57	5.7
58	5.8
59	5.9
60	6
61	6.1
62	6.2
63	6.3
64	6.4
65	6.5
66	6.6
67	6.7
68	6.8
69	6.9
70	7
71	7.1
72	7.2
73	7.3
74	7.4
75	7.5
76	7.6
77	7.7
78	7.8
79	7.9
80	8
81	8.1
82	8.2
83	8.3
84	8.4
85	8.5
86	8.6
87	8.7
88	8.8
89	8.9
90	9
91	9.1
92	9.2
93	9.3
94	9.4
95	9.5
96	9.6
97	9.7
98	9.8
99	9.9
100	10

Seq	SeriesValue
1	0.1
2	0.2
3	0.3
4	0.4
5	0.5
6	0.6
7	0.7
8	0.8
9	0.9
10	1
11	1.1
12	1.2
13	1.3
14	1.4
15	1.5
16	1.6
17	1.7
18	1.8
19	1.9
20	2
21	2.1
22	2.2
23	2.3
24	2.4
25	2.5
26	2.6
27	2.7
28	2.8
29	2.9
30	3
31	3.1
32	3.2
33	3.3
34	3.4
35	3.5
36	3.6
37	3.7
38	3.8
39	3.9
40	4
41	4.1
42	4.2
43	4.3
44	4.4
45	4.5
46	4.6
47	4.7
48	4.8
49	4.9
50	5
51	5.1
52	5.2
53	5.3
54	5.4
55	5.5
56	5.6
57	5.7
58	5.8
59	5.9
60	6
61	6.1
62	6.2
63	6.3
64	6.4
65	6.5
66	6.6
67	6.7
68	6.8
69	6.9
70	7
71	7.1
72	7.2
73	7.3
74	7.4
75	7.5
76	7.6
77	7.7
78	7.8
79	7.9
80	8
81	8.1
82	8.2
83	8.3
84	8.4
85	8.5
86	8.6
87	8.7
88	8.8
89	8.9
90	9
91	9.1
92	9.2
93	9.3
94	9.4
95	9.5
96	9.6
97	9.7
98	9.8
99	9.9
100	10

Resources

SQL Server SeriesFloat Function

Description

Syntax

Arguments

@StartValue

@StopValue

@StepValue

@MaxIterations

@SeriesType

Return Type

Remarks

Examples

Seq	SeriesValue
1	0.1
2	0.2
3	0.3
4	0.4
5	0.5
6	0.6
7	0.7
8	0.8
9	0.9
10	1
11	1.1
12	1.2
13	1.3
14	1.4
15	1.5
16	1.6
17	1.7
18	1.8
19	1.9
20	2
21	2.1
22	2.2
23	2.3
24	2.4
25	2.5
26	2.6
27	2.7
28	2.8
29	2.9
30	3
31	3.1
32	3.2
33	3.3
34	3.4
35	3.5
36	3.6
37	3.7
38	3.8
39	3.9
40	4
41	4.1
42	4.2
43	4.3
44	4.4
45	4.5
46	4.6
47	4.7
48	4.8
49	4.9
50	5
51	5.1
52	5.2
53	5.3
54	5.4
55	5.5
56	5.6
57	5.7
58	5.8
59	5.9
60	6
61	6.1
62	6.2
63	6.3
64	6.4
65	6.5
66	6.6
67	6.7
68	6.8
69	6.9
70	7
71	7.1
72	7.2
73	7.3
74	7.4
75	7.5
76	7.6
77	7.7
78	7.8
79	7.9
80	8
81	8.1
82	8.2
83	8.3
84	8.4
85	8.5
86	8.6
87	8.7
88	8.8
89	8.9
90	9
91	9.1
92	9.2
93	9.3
94	9.4
95	9.5
96	9.6
97	9.7
98	9.8
99	9.9
100	10