INTERP_q

Updated 2023-10-17 19:13:18.977000

Syntax

SELECT [westclintech].[wct].[INTERP_q] (
   <@XY_RangeQuery, nvarchar(4000),>
 ,<@New_x, float,>)

Description

Use the scalar function INTERP_q to calculate the interpolated value of y given x in the array (x, y) using dynamic SQL. INTERP_q uses linear interpolation. INTERP_q is not sensitive to the order of the array (x, y).

Arguments

@New_x

the new x-value for which you want trend to calculate the y-value. @New_x is an expression of type float or of a type that can be implicitly converted to float.

@XY_RangeQuery

the select statement, as text, used to determine the known x- and y-values to be used in this function. Data returned from the @XY_RangeQuery select must be of the type float or of a type that implicitly converts to float.

Return Type

float

Remarks

If the number of known-y data points is not equal to the number of known-x data points, INTERP_q will return an error.

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

For cubic spline interpolation use the SPLINE_q function.

If @New_x is less than the smallest value or greater than the largest x-value in the array, an error will be returned.

If NULL is in the array and used in the interpolation calculation, an error will be returned.

Examples

Interpolation on a table that is in descending order:

create table #xy
(
    [x] [float] NULL,
    [y] [float] NULL
);
INSERT INTO #xy
VALUES
(16, 65536);
INSERT INTO #xy
VALUES
(14, 16384);
INSERT INTO #xy
VALUES
(12, 4096);
INSERT INTO #xy
VALUES
(10, 1024);
INSERT INTO #xy
VALUES
(8, 256);
INSERT INTO #xy
VALUES
(6, 64);
INSERT INTO #xy
VALUES
(4, 16);
INSERT INTO #xy
VALUES
(2, 4);
INSERT INTO #xy
VALUES
(0, 1);
INSERT INTO #xy
VALUES
(-2, 0.25);
INSERT INTO #xy
VALUES
(-4, 0.0625);
INSERT INTO #xy
VALUES
(-6, 0.015625);
INSERT INTO #xy
VALUES
(-8, 0.00390625);
INSERT INTO #xy
VALUES
(-10, 0.0009765625);
INSERT INTO #xy
VALUES
(-12, 0.000244140625);
INSERT INTO #xy
VALUES
(-14, 0.00006103515625);
INSERT INTO #xy
VALUES
(-16, 0.0000152587890625);
SELECT wct.INTERP_q('Select x, y from #xy', 2.5) as INTERP;

This produces the following result.

{"columns":[{"field":"INTERP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INTERP":"7"}]}

Interpolation using dates

create table #xy
(
    [x] [datetime] NULL,
    [y] [float] NULL
);
INSERT INTO #xy
VALUES
('09/30/2009', 0.00041);
INSERT INTO #xy
VALUES
('11/30/2009', 0.00096);
INSERT INTO #xy
VALUES
('03/31/2010', 0.00198);
INSERT INTO #xy
VALUES
('09/30/2010', 0.00362);
INSERT INTO #xy
VALUES
('09/30/2011', 0.00984);
INSERT INTO #xy
VALUES
('09/30/2012', 0.0155);
INSERT INTO #xy
VALUES
('09/30/2014', 0.02449);
INSERT INTO #xy
VALUES
('09/30/2019', 0.03476);
INSERT INTO #xy
VALUES
('09/30/2029', 0.04234);
SELECT wct.INTERP_q('Select cast(x as float)
      ,y from #xy', cast(cast('08/15/2010' as datetime) as float)) as INTERP;

This produces the following result.

{"columns":[{"field":"INTERP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INTERP":"0.00320775956284153"}]}

Interpolation with invalid data in the array

create table #xy
(
    [x] [datetime] NULL,
    [y] [float] NULL
);
INSERT INTO #xy
VALUES
('09/30/2009', 0.00041);
INSERT INTO #xy
VALUES
('11/30/2009', 0.00096);
INSERT INTO #xy
VALUES
('03/31/2010', 0.00198);
INSERT INTO #xy
VALUES
('09/30/2010', NULL);
INSERT INTO #xy
VALUES
('09/30/2011', 0.00984);
INSERT INTO #xy
VALUES
('09/30/2012', 0.0155);
INSERT INTO #xy
VALUES
('09/30/2014', 0.02449);
INSERT INTO #xy
VALUES
('09/30/2019', 0.03476);
INSERT INTO #xy
VALUES
('09/30/2029', 0.04234);
SELECT wct.INTERP_q('Select cast(x as float)
      ,y from #xy', cast(cast('08/15/2010' as datetime) as float)) as INTERP;

This produces the following result.

Msg 6522, Level 16, State 1, Line 33

A .NET Framework error occurred during execution of user-defined routine or aggregate "INTERP_q":

q+c: Invalid 'Known_y' parameter value passed to function 'INTERP'

Known_y interpolation point is NULL.

See Also

SPLINE_q - Calculate the interpolated value of y given x in the array (x, y).

INTERP - Calculate the straight-line interpolated value of y given x in the array (x, y).