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 15A .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'