SeriesFloat

Updated 2023-10-18 19:59:18.197000

Syntax

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

Description

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

Arguments

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

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

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "e1e59bbb-cc97-4375-a5a5-1f2d6e3546ec", "colName": "Seq", "colDatatype": "int", "colDesc": "A monotonically increasing sequence number"}, {"id": "59e42763-c6f6-4c40-9bcd-3e43d0227384", "colName": "SeriesValue", "colDatatype": "float", "colDesc": "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.

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

{"columns":[{"field":"Seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SeriesValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Seq":"1","SeriesValue":"10"},{"Seq":"2","SeriesValue":"6.6667"},{"Seq":"3","SeriesValue":"3.3334"},{"Seq":"4","SeriesValue":"9.99999999997669E-05"},{"Seq":"5","SeriesValue":"-3.3332"},{"Seq":"6","SeriesValue":"-6.6665"},{"Seq":"7","SeriesValue":"-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).

{"columns":[{"field":"Seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SeriesValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Seq":"1","SeriesValue":"0.0787300244340347"},{"Seq":"2","SeriesValue":"0.0393758332540169"},{"Seq":"3","SeriesValue":"0.237071212957181"},{"Seq":"4","SeriesValue":"0.275765785144533"},{"Seq":"5","SeriesValue":"0.765305606073377"},{"Seq":"6","SeriesValue":"0.506849329688982"},{"Seq":"7","SeriesValue":"0.654947900052624"},{"Seq":"8","SeriesValue":"0.0535740512672691"},{"Seq":"9","SeriesValue":"0.774494760099097"},{"Seq":"10","SeriesValue":"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.

{"columns":[{"field":"Seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SeriesValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Seq":"1","SeriesValue":"85.625"},{"Seq":"2","SeriesValue":"58.59375"},{"Seq":"3","SeriesValue":"96.28125"},{"Seq":"4","SeriesValue":"97.6875"},{"Seq":"5","SeriesValue":"87.3125"},{"Seq":"6","SeriesValue":"70.90625"},{"Seq":"7","SeriesValue":"42.46875"},{"Seq":"8","SeriesValue":"98.9375"},{"Seq":"9","SeriesValue":"63.40625"},{"Seq":"10","SeriesValue":"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:

{"columns":[{"field":"seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NORMSINV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"seq":"1","NORMSINV":"0.115115246564977"},{"seq":"2","NORMSINV":"0.0861232660335375"},{"seq":"3","NORMSINV":"-0.51211327862208"},{"seq":"4","NORMSINV":"0.0833024753960621"},{"seq":"5","NORMSINV":"-1.1040088954424"},{"seq":"6","NORMSINV":"0.665494336529001"},{"seq":"7","NORMSINV":"-0.980701775124289"},{"seq":"8","NORMSINV":"0.445878666345121"},{"seq":"9","NORMSINV":"0.300888521636836"},{"seq":"10","NORMSINV":"-0.197957795305958"},{"seq":"11","NORMSINV":"-0.0368872432918503"},{"seq":"12","NORMSINV":"-1.81611011290773"},{"seq":"13","NORMSINV":"0.397721052691346"},{"seq":"14","NORMSINV":"-0.0960160763401366"},{"seq":"15","NORMSINV":"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.

{"columns":[{"field":"seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"seq":"1","":"106.529474006228"},{"seq":"2","":"123.00857181537"},{"seq":"3","":"80.099070876261"},{"seq":"4","":"98.0276399646496"},{"seq":"5","":"91.7737036409259"},{"seq":"6","":"108.09737139573"},{"seq":"7","":"85.050164912141"},{"seq":"8","":"122.951511599054"},{"seq":"9","":"115.077771376198"},{"seq":"10","":"93.4072825124728"},{"seq":"11","":"86.2396638843069"},{"seq":"12","":"92.7253168560602"},{"seq":"13","":"103.122155353833"},{"seq":"14","":"91.1914297396313"},{"seq":"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.

{"columns":[{"field":"seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"seq":"1","":"13.5629377694821"},{"seq":"2","":"11.1932994911787"},{"seq":"3","":"10.9438339532567"},{"seq":"4","":"9.90162997348385"},{"seq":"5","":"14.6773466847694"},{"seq":"6","":"10.3188014158663"},{"seq":"7","":"10.7589410774399"},{"seq":"8","":"10.637936085491"},{"seq":"9","":"10.1797678083148"},{"seq":"10","":"8.82692712547266"},{"seq":"11","":"10.700105146452"},{"seq":"12","":"10.4919966511707"},{"seq":"13","":"5.94455182109008"},{"seq":"14","":"10.9149500274106"},{"seq":"15","":"8.01495898641042"}]}