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 33A .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).