# SQL Server PROB function

Updated: 9 August 2010

Use PROB_q to calculate the probability that values in a range are between two limits.
Syntax
SELECT [wctStatistics].[wct].[PROB_q] (
<@Prob_range_X_range_RangeQuery, nvarchar(4000),>
,<@Lower_limit, float,>
,<@Upper_limit, float,>)
Arguments
@Prob_range_X_range_RangeQuery
the select statement, as text, used to obtain the probabilities and x-values for the PROB_q calculation.
@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.

Return Types
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

----------------------
0.2

(1 row(s) affected)

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

----------------------
0.52

(1 row(s) affected)