SPLINE_q

Updated 2023-10-18 15:17:44.383000

Syntax

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

Description

Use the scalar function SPLINE_q to calculate the interpolated value of y given x in the array (x, y) using dynamic SQL. SPLINE_q uses cubic spline interpolation. SPLINE_q is sensitive to the order of the array (x, y); to provide meaningful results, data should be in x-value order, ascending.

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, SPLINE_q will return an error.

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

For linear interpolation use the INTERP_q function.

If NULL is in the array 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.SPLINE_q('Select x, y from #xy order by x', 2.5) as SPLINE;

This produces the following result.

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

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.SPLINE_q('Select cast(x as float)
      ,y from #XY', cast(cast('08/15/2010' as datetime) as float)) as SPLINE;
DROP TABLE #XY;

This produces the following result.

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

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.SPLINE_q('Select cast(x as float)
      ,y from #xy', cast(cast('08/15/2010' as datetime) as float)) as SPLINE;

This produces the following result:

Msg 6522, Level 16, State 1, Line 15

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

q+c: Invalid 'Known_x' parameter value passed to function 'SPLINE'