PROB_q

Updated 2023-11-01 22:50:41.947000

Syntax

SELECT [westclintech].[wct].[PROB_q] (
  <@Prob_range_X_range_RangeQuery, nvarchar(4000),>
 ,<@Lower_limit, float,>
 ,<@Upper_limit, float,>)

Description

Use the scalar function PROB_q to calculate the probability that values in a range are between two limits.

Arguments

@Lower_limit

is the lower bound on the value for which you want a probability. @Lower_limit is an expression of type float or of a type that can be implicitly converted to float.

@Upper_limit

is the upper bound on the value for which you want a probability. @Upper_limit is an expression of type float or of a type that can be implicitly converted to float.

@Prob_range_X_range_RangeQuery

the select statement, as text, used to obtain the probabilities and x-values for the PROB_q calculation.

Return Type

float

Remarks

If any value in the @Prob_range_ ColumnName = 0 or if any value in the @Prob_range_ ColumnName >1, PROB_q returns an error.

If the sum of the values in the @Prob_range_ ColumnName <> 1, PROB_q returns an error.

If @Upper_limit is NULL, PROB_q returns that probability of being equal to the lower limit.

If the number of data point in @Prob_range_ColumnName <> the number of data points in @X_range_ColumnName, PROB_q returns an error.

No GROUP BY is required for this function even though it produces aggregated results.

Examples

CREATE TABLE #p1 (
    [x] [float] NOT NULL,
    [prob] [float] NOT NULL);
INSERT INTO #p1
VALUES (0, 0.1);
INSERT INTO #p1
VALUES (1, 0.01);
INSERT INTO #p1
VALUES (2, 0.15);
INSERT INTO #p1
VALUES (3, 0.25);
INSERT INTO #p1
VALUES (5, 0.05);
INSERT INTO #p1
VALUES (5, 0.06);
INSERT INTO #p1
VALUES (6, 0.07);
INSERT INTO #p1
VALUES (7, 0.12);
INSERT INTO #p1
VALUES (8, 0.09);
INSERT INTO #p1
VALUES (0, 0.1);

To return the the probability of x being equal to zero:

SELECT wct.PROB_q('SELECT prob, x from #p1', 0, 0);

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.2"}]}

To return the probability of x being between 1 and 5:

SELECT wct.PROB_q('SELECT prob, x from #p1', 1, 5);

This produces the following result

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.52"}]}