SQL Server MovingINTERCEPT Function

Updated 2023-11-13 21:20:30.677000

Description

Use the scalar function MovingINTERCEPT to calculate the intercept of a series of x- and y-values within a resultant table or partition, without the need for a self-join. The intercept value is calculated for each value from the first value in the window to the last value in the window. If the column values are presented to the functions out of order, an error message will be generated.

Syntax

SELECT [westclintech].[wct].[MovingINTERCEPT](
  <@y, float,>
 ,<@x, float,>
 ,<@Offset, int,>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>)

Arguments

@Offset

specifies the window size. @Offset is an expression of type int or of a type that can be implicitly converted to int.

@RowNum

the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.

@Id

a unique identifier for the MovingINTERCEPT calculation. @Id allows you to specify multiple MovingINTERCEPT calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.

Return Type

float

Remarks

If @Id is NULL then @Id = 0.

@RowNum must be in ascending order.

To calculate the intercept from the first row in a data set or partition use the RunningINTERCEPT function.

If @RowNum = 1 then MovingINTERCEPT is NULL.

Set @X to NULL to have the function maintain a constant set of x-values in the range 1 to window-size.

To calculate a single intercept value for a set of data use the INTERCEPT function.

There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.

Examples

In this example we will calculate the intercept, over time, in the closing prices for some stock indices, with the offset set to 9, meaning that the intercept will be calculated using the current row and the nine preceding rows. We will create a temporary table, #p, populate it with some data and then run the SELECT.

--Create the temporary table
CREATE TABLE #p
(
    ticker varchar(4),
    date_trade datetime,
    price_open money,
    price_high money,
    price_low money,
    price_close money,
    volume bigint,
    price_adj_close money,
    PRIMARY KEY (
                    ticker,
                    date_trade
                )
);
--Populate the table with some data
INSERT INTO #p
VALUES
('GSPC', '2012-10-26', 1412.97, 1417.09, 1403.28, 1411.94, 3284910000, 1411.94);
INSERT INTO #p
VALUES
('GSPC', '2012-10-25', 1409.74, 1421.12, 1405.14, 1412.97, 3512640000, 1412.97);
INSERT INTO #p
VALUES
('GSPC', '2012-10-24', 1413.2, 1420.04, 1407.1, 1408.75, 3385970000, 1408.75);
INSERT INTO #p
VALUES
('GSPC', '2012-10-23', 1433.74, 1433.74, 1407.56, 1413.11, 3587670000, 1413.11);
INSERT INTO #p
VALUES
('GSPC', '2012-10-22', 1433.21, 1435.46, 1422.06, 1433.81, 3216220000, 1433.81);
INSERT INTO #p
VALUES
('GSPC', '2012-10-19', 1457.34, 1457.34, 1429.85, 1433.19, 3875170000, 1433.19);
INSERT INTO #p
VALUES
('GSPC', '2012-10-18', 1460.94, 1464.02, 1452.63, 1457.34, 3880030000, 1457.34);
INSERT INTO #p
VALUES
('GSPC', '2012-10-17', 1454.22, 1462.2, 1453.35, 1460.91, 3655320000, 1460.91);
INSERT INTO #p
VALUES
('GSPC', '2012-10-16', 1440.31, 1455.51, 1440.31, 1454.92, 3568770000, 1454.92);
INSERT INTO #p
VALUES
('GSPC', '2012-10-15', 1428.75, 1441.31, 1427.24, 1440.13, 3483810000, 1440.13);
INSERT INTO #p
VALUES
('GSPC', '2012-10-12', 1432.84, 1438.43, 1425.53, 1428.59, 3134750000, 1428.59);
INSERT INTO #p
VALUES
('GSPC', '2012-10-11', 1432.82, 1443.9, 1432.82, 1432.84, 3672540000, 1432.84);
INSERT INTO #p
VALUES
('GSPC', '2012-10-10', 1441.48, 1442.52, 1430.64, 1432.56, 3225060000, 1432.56);
INSERT INTO #p
VALUES
('GSPC', '2012-10-09', 1455.9, 1455.9, 1441.18, 1441.48, 3216320000, 1441.48);
INSERT INTO #p
VALUES
('GSPC', '2012-10-08', 1460.93, 1460.93, 1453.1, 1455.88, 2328720000, 1455.88);
INSERT INTO #p
VALUES
('GSPC', '2012-10-05', 1461.4, 1470.96, 1456.89, 1460.93, 3172940000, 1460.93);
INSERT INTO #p
VALUES
('GSPC', '2012-10-04', 1451.08, 1463.14, 1451.08, 1461.4, 3615860000, 1461.4);
INSERT INTO #p
VALUES
('GSPC', '2012-10-03', 1446.05, 1454.3, 1441.99, 1450.99, 3531640000, 1450.99);
INSERT INTO #p
VALUES
('GSPC', '2012-10-02', 1444.99, 1451.52, 1439.01, 1445.75, 3321790000, 1445.75);
INSERT INTO #p
VALUES
('GSPC', '2012-10-01', 1440.9, 1457.14, 1440.9, 1444.49, 3505080000, 1444.49);
INSERT INTO #p
VALUES
('GSPC', '2012-09-28', 1447.13, 1447.13, 1435.6, 1440.67, 3509230000, 1440.67);
INSERT INTO #p
VALUES
('GSPC', '2012-09-27', 1433.36, 1450.2, 1433.36, 1447.15, 3150330000, 1447.15);
INSERT INTO #p
VALUES
('GSPC', '2012-09-26', 1441.6, 1441.6, 1430.53, 1433.32, 3565380000, 1433.32);
INSERT INTO #p
VALUES
('GSPC', '2012-09-25', 1456.94, 1463.24, 1441.59, 1441.59, 3739900000, 1441.59);
INSERT INTO #p
VALUES
('GSPC', '2012-09-24', 1459.76, 1460.72, 1452.06, 1456.89, 3008920000, 1456.89);
INSERT INTO #p
VALUES
('GSPC', '2012-09-21', 1460.34, 1467.07, 1459.51, 1460.15, 4833870000, 1460.15);
INSERT INTO #p
VALUES
('GSPC', '2012-09-20', 1461.05, 1461.23, 1449.98, 1460.26, 3382520000, 1460.26);
INSERT INTO #p
VALUES
('GSPC', '2012-09-19', 1459.5, 1465.15, 1457.88, 1461.05, 3451360000, 1461.05);
INSERT INTO #p
VALUES
('GSPC', '2012-09-18', 1461.19, 1461.47, 1456.13, 1459.32, 3377390000, 1459.32);
INSERT INTO #p
VALUES
('GSPC', '2012-09-17', 1465.42, 1465.63, 1457.55, 1461.19, 3482430000, 1461.19);
INSERT INTO #p
VALUES
('GSPC', '2012-09-14', 1460.07, 1474.51, 1460.07, 1465.77, 5041990000, 1465.77);
INSERT INTO #p
VALUES
('GSPC', '2012-09-13', 1436.56, 1463.76, 1435.34, 1459.99, 4606550000, 1459.99);
INSERT INTO #p
VALUES
('GSPC', '2012-09-12', 1433.56, 1439.15, 1432.99, 1436.56, 3641200000, 1436.56);
INSERT INTO #p
VALUES
('GSPC', '2012-09-11', 1429.13, 1437.76, 1429.13, 1433.56, 3509630000, 1433.56);
INSERT INTO #p
VALUES
('GSPC', '2012-09-10', 1437.92, 1438.74, 1428.98, 1429.08, 3223670000, 1429.08);
INSERT INTO #p
VALUES
('GSPC', '2012-09-07', 1432.12, 1437.92, 1431.45, 1437.92, 3717620000, 1437.92);
INSERT INTO #p
VALUES
('GSPC', '2012-09-06', 1403.74, 1432.12, 1403.74, 1432.12, 3952870000, 1432.12);
INSERT INTO #p
VALUES
('GSPC', '2012-09-05', 1404.94, 1408.81, 1401.25, 1403.44, 3389110000, 1403.44);
INSERT INTO #p
VALUES
('GSPC', '2012-09-04', 1406.54, 1409.31, 1396.56, 1404.94, 3200310000, 1404.94);
INSERT INTO #p
VALUES
('GSPC', '2012-08-31', 1400.07, 1413.09, 1398.96, 1406.58, 2938250000, 1406.58);
INSERT INTO #p
VALUES
('GSPC', '2012-08-30', 1410.08, 1410.08, 1397.01, 1399.48, 2530280000, 1399.48);
INSERT INTO #p
VALUES
('GSPC', '2012-08-29', 1409.32, 1413.95, 1406.57, 1410.49, 2571220000, 1410.49);
INSERT INTO #p
VALUES
('GSPC', '2012-08-28', 1410.44, 1413.63, 1405.59, 1409.3, 2629090000, 1409.3);
INSERT INTO #p
VALUES
('GSPC', '2012-08-27', 1411.13, 1416.17, 1409.11, 1410.44, 2472500000, 1410.44);
INSERT INTO #p
VALUES
('GSPC', '2012-08-24', 1401.99, 1413.46, 1398.04, 1411.13, 2598790000, 1411.13);
INSERT INTO #p
VALUES
('DJI', '2012-10-26', 13104.22, 13151.72, 13040.17, 13107.21, 1346400, 13107.21);
INSERT INTO #p
VALUES
('DJI', '2012-10-25', 13079.64, 13214.11, 13017.37, 13103.68, 1145900, 13103.68);
INSERT INTO #p
VALUES
('DJI', '2012-10-24', 13103.53, 13155.21, 13063.63, 13077.34, 1106700, 13077.34);
INSERT INTO #p
VALUES
('DJI', '2012-10-23', 13344.9, 13344.9, 13083.28, 13102.53, 1222200, 13102.53);
INSERT INTO #p
VALUES
('DJI', '2012-10-22', 13344.28, 13368.55, 13235.15, 13345.89, 1218800, 13345.89);
INSERT INTO #p
VALUES
('DJI', '2012-10-19', 13545.33, 13545.49, 13312.22, 13343.51, 2390800, 13343.51);
INSERT INTO #p
VALUES
('DJI', '2012-10-18', 13553.24, 13588.73, 13510.93, 13548.94, 1284100, 13548.94);
INSERT INTO #p
VALUES
('DJI', '2012-10-17', 13539.63, 13561.65, 13468.9, 13557, 1355700, 13557);
INSERT INTO #p
VALUES
('DJI', '2012-10-16', 13423.84, 13556.37, 13423.76, 13551.78, 1134500, 13551.78);
INSERT INTO #p
VALUES
('DJI', '2012-10-15', 13329.54, 13437.66, 13325.93, 13424.23, 1148800, 13424.23);
INSERT INTO #p
VALUES
('DJI', '2012-10-12', 13325.62, 13401.32, 13296.43, 13328.85, 1137400, 13328.85);
INSERT INTO #p
VALUES
('DJI', '2012-10-11', 13346.28, 13428.49, 13326.12, 13326.39, 866300, 13326.39);
INSERT INTO #p
VALUES
('DJI', '2012-10-10', 13473.53, 13478.83, 13327.62, 13344.97, 1011200, 13344.97);
INSERT INTO #p
VALUES
('DJI', '2012-10-09', 13582.88, 13592.33, 13473.31, 13473.53, 1036300, 13473.53);
INSERT INTO #p
VALUES
('DJI', '2012-10-08', 13589.26, 13610.38, 13552.09, 13583.65, 713000, 13583.65);
INSERT INTO #p
VALUES
('DJI', '2012-10-05', 13569.18, 13661.87, 13568.75, 13610.15, 1155000, 13610.15);
INSERT INTO #p
VALUES
('DJI', '2012-10-04', 13495.18, 13594.33, 13495.18, 13575.36, 1063900, 13575.36);
INSERT INTO #p
VALUES
('DJI', '2012-10-03', 13479.21, 13536.27, 13439.12, 13494.61, 1038900, 13494.61);
INSERT INTO #p
VALUES
('DJI', '2012-10-02', 13515.3, 13567.06, 13424.92, 13482.36, 907300, 13482.36);
INSERT INTO #p
VALUES
('DJI', '2012-10-01', 13437.66, 13598.25, 13437.66, 13515.11, 1061200, 13515.11);
INSERT INTO #p
VALUES
('DJI', '2012-09-28', 13485.89, 13487.66, 13367.27, 13437.13, 1469500, 13437.13);
INSERT INTO #p
VALUES
('DJI', '2012-09-27', 13413.47, 13522.83, 13413.47, 13485.97, 1139900, 13485.97);
INSERT INTO #p
VALUES
('DJI', '2012-09-26', 13458.63, 13480.37, 13406.91, 13413.51, 1243500, 13413.51);
INSERT INTO #p
VALUES
('DJI', '2012-09-25', 13559.92, 13620.21, 13457.25, 13457.55, 1386300, 13457.55);
INSERT INTO #p
VALUES
('DJI', '2012-09-24', 13577.85, 13601.9, 13521.68, 13558.92, 1203700, 13558.92);
INSERT INTO #p
VALUES
('DJI', '2012-09-21', 13597.24, 13647.1, 13571.53, 13579.47, 4296100, 13579.47);
INSERT INTO #p
VALUES
('DJI', '2012-09-20', 13575.17, 13599.02, 13503, 13596.93, 1179100, 13596.93);
INSERT INTO #p
VALUES
('DJI', '2012-09-19', 13565.41, 13626.48, 13556.74, 13577.96, 1162100, 13577.96);
INSERT INTO #p
VALUES
('DJI', '2012-09-18', 13552.33, 13582.12, 13517.81, 13564.64, 1207200, 13564.64);
INSERT INTO #p
VALUES
('DJI', '2012-09-17', 13588.57, 13593.15, 13526.67, 13553.1, 1280200, 13553.1);
INSERT INTO #p
VALUES
('DJI', '2012-09-14', 13540.4, 13653.24, 13533.94, 13593.37, 1851600, 13593.37);
INSERT INTO #p
VALUES
('DJI', '2012-09-13', 13329.71, 13573.33, 13325.11, 13539.86, 1517700, 13539.86);
INSERT INTO #p
VALUES
('DJI', '2012-09-12', 13321.62, 13373.62, 13317.52, 13333.35, 1115200, 13333.35);
INSERT INTO #p
VALUES
('DJI', '2012-09-11', 13254.6, 13354.34, 13253.21, 13323.36, 1049200, 13323.36);
INSERT INTO #p
VALUES
('DJI', '2012-09-10', 13308.56, 13324.1, 13251.39, 13254.29, 1238100, 13254.29);
INSERT INTO #p
VALUES
('DJI', '2012-09-07', 13289.53, 13320.27, 13266.22, 13306.64, 1422100, 13306.64);
INSERT INTO #p
VALUES
('DJI', '2012-09-06', 13045.23, 13294.13, 13045.08, 13292, 1286500, 13292);
INSERT INTO #p
VALUES
('DJI', '2012-09-05', 13036.09, 13095.91, 13018.74, 13047.48, 925500, 13047.48);
INSERT INTO #p
VALUES
('DJI', '2012-09-04', 13092.15, 13092.39, 12977.09, 13035.94, 1039200, 13035.94);
INSERT INTO #p
VALUES
('DJI', '2012-08-31', 13002.72, 13151.87, 13002.64, 13090.84, 1197800, 13090.84);
INSERT INTO #p
VALUES
('DJI', '2012-08-30', 13101.29, 13101.37, 12978.91, 13000.71, 899800, 13000.71);
INSERT INTO #p
VALUES
('DJI', '2012-08-29', 13103.46, 13144.81, 13081.27, 13107.48, 915300, 13107.48);
INSERT INTO #p
VALUES
('DJI', '2012-08-28', 13122.74, 13147.32, 13081.12, 13102.99, 816300, 13102.99);
INSERT INTO #p
VALUES
('DJI', '2012-08-27', 13157.74, 13176.17, 13115.46, 13124.67, 960700, 13124.67);
INSERT INTO #p
VALUES
('DJI', '2012-08-24', 13052.82, 13175.51, 13027.2, 13157.97, 880300, 13157.97);
INSERT INTO #p
VALUES
('IXIC', '2012-10-26', 2986.05, 2999.14, 2961.16, 2987.95, 1839700000, 2987.95);
INSERT INTO #p
VALUES
('IXIC', '2012-10-25', 3005.04, 3007.71, 2975.98, 2986.12, 1922660000, 2986.12);
INSERT INTO #p
VALUES
('IXIC', '2012-10-24', 3011.82, 3012.95, 2978.73, 2981.7, 1967000000, 2981.7);
INSERT INTO #p
VALUES
('IXIC', '2012-10-23', 2989.44, 3006.59, 2974.07, 2990.46, 1830840000, 2990.46);
INSERT INTO #p
VALUES
('IXIC', '2012-10-22', 3005.92, 3020.61, 2995.78, 3016.96, 1654130000, 3016.96);
INSERT INTO #p
VALUES
('IXIC', '2012-10-19', 3066.56, 3066.56, 3000.27, 3005.62, 2225580000, 3005.62);
INSERT INTO #p
VALUES
('IXIC', '2012-10-18', 3097.77, 3102.56, 3065.24, 3072.87, 2043290000, 3072.87);
INSERT INTO #p
VALUES
('IXIC', '2012-10-17', 3091.38, 3112.45, 3088.05, 3104.12, 1770920000, 3104.12);
INSERT INTO #p
VALUES
('IXIC', '2012-10-16', 3073.21, 3102.97, 3070.25, 3101.17, 1736930000, 3101.17);
INSERT INTO #p
VALUES
('IXIC', '2012-10-15', 3053.21, 3066.31, 3037.27, 3064.18, 1563440000, 3064.18);
INSERT INTO #p
VALUES
('IXIC', '2012-10-12', 3049.08, 3061.77, 3039.58, 3044.11, 1524840000, 3044.11);
INSERT INTO #p
VALUES
('IXIC', '2012-10-11', 3075.89, 3078.08, 3047.14, 3049.41, 1595020000, 3049.41);
INSERT INTO #p
VALUES
('IXIC', '2012-10-10', 3066.25, 3071.57, 3046.78, 3051.78, 1788970000, 3051.78);
INSERT INTO #p
VALUES
('IXIC', '2012-10-09', 3108.01, 3108.01, 3062.52, 3065.02, 1645740000, 3065.02);
INSERT INTO #p
VALUES
('IXIC', '2012-10-08', 3121.33, 3125.49, 3107.57, 3112.35, 1186260000, 3112.35);
INSERT INTO #p
VALUES
('IXIC', '2012-10-05', 3161.21, 3171.46, 3130.76, 3136.19, 1607940000, 3136.19);
INSERT INTO #p
VALUES
('IXIC', '2012-10-04', 3142.38, 3153.48, 3132.56, 3149.46, 1585190000, 3149.46);
INSERT INTO #p
VALUES
('IXIC', '2012-10-03', 3130.85, 3142.36, 3115.04, 3135.23, 1704050000, 3135.23);
INSERT INTO #p
VALUES
('IXIC', '2012-10-02', 3127.73, 3131.64, 3101.64, 3120.04, 1609570000, 3120.04);
INSERT INTO #p
VALUES
('IXIC', '2012-10-01', 3130.31, 3146.99, 3103.89, 3113.53, 1758170000, 3113.53);
INSERT INTO #p
VALUES
('IXIC', '2012-09-28', 3125.31, 3132.51, 3109.91, 3116.23, 1864640000, 3116.23);
INSERT INTO #p
VALUES
('IXIC', '2012-09-27', 3105.87, 3142.02, 3098.46, 3136.6, 1691800000, 3136.6);
INSERT INTO #p
VALUES
('IXIC', '2012-09-26', 3113.4, 3114.54, 3080.28, 3093.7, 1738010000, 3093.7);
INSERT INTO #p
VALUES
('IXIC', '2012-09-25', 3170.37, 3176.3, 3117.73, 3117.73, 1975470000, 3117.73);
INSERT INTO #p
VALUES
('IXIC', '2012-09-24', 3155.35, 3167.74, 3150.71, 3160.78, 1704860000, 3160.78);
INSERT INTO #p
VALUES
('IXIC', '2012-09-21', 3194.86, 3196.93, 3178.09, 3179.96, 2526250000, 3179.96);
INSERT INTO #p
VALUES
('IXIC', '2012-09-20', 3166.84, 3178.45, 3156.46, 3175.96, 1809130000, 3175.96);
INSERT INTO #p
VALUES
('IXIC', '2012-09-19', 3179.04, 3189.35, 3170.29, 3182.62, 1850920000, 3182.62);
INSERT INTO #p
VALUES
('IXIC', '2012-09-18', 3173.62, 3179.37, 3169.41, 3177.8, 1707200000, 3177.8);
INSERT INTO #p
VALUES
('IXIC', '2012-09-17', 3183.4, 3183.4, 3168.63, 3178.67, 1485390000, 3178.67);
INSERT INTO #p
VALUES
('IXIC', '2012-09-14', 3166.24, 3195.67, 3164.26, 3183.95, 1984720000, 3183.95);
INSERT INTO #p
VALUES
('IXIC', '2012-09-13', 3117.66, 3167.63, 3112.62, 3155.83, 1870050000, 3155.83);
INSERT INTO #p
VALUES
('IXIC', '2012-09-12', 3115.33, 3120.12, 3098.82, 3114.31, 1689140000, 3114.31);
INSERT INTO #p
VALUES
('IXIC', '2012-09-11', 3105.02, 3117.86, 3099.1, 3104.53, 1586250000, 3104.53);
INSERT INTO #p
VALUES
('IXIC', '2012-09-10', 3131.34, 3133.89, 3102.76, 3104.02, 1575370000, 3104.02);
INSERT INTO #p
VALUES
('IXIC', '2012-09-07', 3133.22, 3139.61, 3128.17, 3136.42, 1740640000, 3136.42);
INSERT INTO #p
VALUES
('IXIC', '2012-09-06', 3087.94, 3135.81, 3087.67, 3135.81, 1918900000, 3135.81);
INSERT INTO #p
VALUES
('IXIC', '2012-09-05', 3072.58, 3082.75, 3062.54, 3069.27, 1495030000, 3069.27);
INSERT INTO #p
VALUES
('IXIC', '2012-09-04', 3063.25, 3082.26, 3040.24, 3075.06, 1505960000, 3075.06);
INSERT INTO #p
VALUES
('IXIC', '2012-08-31', 3069.64, 3078.52, 3040.59, 3066.96, 1394760000, 3066.96);
INSERT INTO #p
VALUES
('IXIC', '2012-08-30', 3066.73, 3067.54, 3045.92, 3048.71, 1216640000, 3048.71);
INSERT INTO #p
VALUES
('IXIC', '2012-08-29', 3078.05, 3087.24, 3067.62, 3081.19, 1282900000, 3081.19);
INSERT INTO #p
VALUES
('IXIC', '2012-08-28', 3069.4, 3083.19, 3063.65, 3077.14, 1364740000, 3077.14);
INSERT INTO #p
VALUES
('IXIC', '2012-08-27', 3083.62, 3085.81, 3068.13, 3073.19, 1383530000, 3073.19);
INSERT INTO #p
VALUES
('IXIC', '2012-08-24', 3045.22, 3076.8, 3042.22, 3069.79, 1349740000, 3069.79);
--Calculate the INTERCEPT for DJI
SELECT ticker,
       cast(date_trade as date) as [trade date],
       price_close as [Closing Price],
       wct.MovingINTERCEPT(
                              price_close,
                              convert(float, date_trade),
                              9,
                              ROW_NUMBER() over (ORDER BY date_trade ASC),
                              NULL
                          ) as [INTERCEPT]
FROM #p
WHERE ticker = 'DJI'
ORDER BY date_trade ASC;
--Clean up
DROP TABLE #p;

This produces the following result.

ticker	trade date	Closing Price	INTERCEPT
DJI	2012-08-24	13157.97	NULL
DJI	2012-08-27	13124.67	469845.270435983
DJI	2012-08-28	13102.99	553556.67002926
DJI	2012-08-29	13107.48	471489.717612114
DJI	2012-08-30	13000.71	886730.362595414
DJI	2012-08-31	13090.84	643747.834434902
DJI	2012-09-04	13035.94	496716.219783845
DJI	2012-09-05	13047.48	409829.67629724
DJI	2012-09-06	13292.00	-44403.3721036542
DJI	2012-09-07	13306.64	-295181.00258685
DJI	2012-09-10	13254.29	-542008.459971776
DJI	2012-09-11	13323.36	-735444.321404394
DJI	2012-09-12	13333.35	-870702.046392294
DJI	2012-09-13	13539.86	-1246747.13725805
DJI	2012-09-14	13593.37	-1467291.2763445
DJI	2012-09-17	13553.10	-1644499.73058225
DJI	2012-09-18	13564.64	-1411511.43672222
DJI	2012-09-19	13577.96	-1101890.88743121
DJI	2012-09-20	13596.93	-1130994.98631007
DJI	2012-09-21	13579.47	-1106566.73528378
DJI	2012-09-24	13558.92	-664847.003184797
DJI	2012-09-25	13457.55	-181452.843235763
DJI	2012-09-26	13413.51	362171.402174615
DJI	2012-09-27	13485.97	478072.986026638
DJI	2012-09-28	13437.13	575982.207032812
DJI	2012-10-01	13515.11	460034.807496188
DJI	2012-10-02	13482.36	410220.359750334
DJI	2012-10-03	13494.61	312144.764872803
DJI	2012-10-04	13575.36	32885.1692340084
DJI	2012-10-05	13610.15	-334342.337812188
DJI	2012-10-08	13583.65	-526223.972706134
DJI	2012-10-09	13473.53	-359831.342292858
DJI	2012-10-10	13344.97	47681.5850020345
DJI	2012-10-11	13326.39	335191.656204434
DJI	2012-10-12	13328.85	748864.957925944
DJI	2012-10-15	13424.23	664637.86480042
DJI	2012-10-16	13551.78	455683.512050535
DJI	2012-10-17	13557.00	287604.388866922
DJI	2012-10-18	13548.94	3250.36464128776
DJI	2012-10-19	13343.51	-121615.807152873
DJI	2012-10-22	13345.89	-155012.169428583
DJI	2012-10-23	13102.53	276605.564481351
DJI	2012-10-24	13077.34	810032.45704968
DJI	2012-10-25	13103.68	1312260.55141121
DJI	2012-10-26	13107.21	1907476.67090713

Notice that in the previous example that the interval between dates is unequal because there are no prices for the weekends and holidays. Additionally, since we start so far away from the origin, the intercept value is misleading. To eliminate both problems, we can set @X to NULL which will have the effect of equally spacing the x-values and starting almost at the point of origin.

SELECT ticker,
       cast(date_trade as date) as [trade date],
       price_close as [Closing Price],
       wct.MovingINTERCEPT(price_close, NULL, 9, ROW_NUMBER() over (ORDER BY 
                 date_trade ASC), NULL) as [INTERCEPT]
FROM #p
WHERE ticker = 'DJI'
ORDER BY date_trade ASC;

This produces the following result.

ticker	trade date	Closing Price	INTERCEPT
DJI	2012-08-24	13157.97	NULL
DJI	2012-08-27	13124.67	13191.27
DJI	2012-08-28	13102.99	13183.5233333333
DJI	2012-08-29	13107.48	13166.565
DJI	2012-08-30	13000.71	13198.277
DJI	2012-08-31	13090.84	13167.7473333333
DJI	2012-09-04	13035.94	13165.2328571429
DJI	2012-09-05	13047.48	13156.3832142857
DJI	2012-09-06	13292.00	13093.8580555555
DJI	2012-09-07	13306.64	13056.4286666667
DJI	2012-09-10	13254.29	13016.2993333333
DJI	2012-09-11	13323.36	12984.3173333333
DJI	2012-09-12	13333.35	12973.7006666667
DJI	2012-09-13	13539.86	12935.044
DJI	2012-09-14	13593.37	12964.3366666667
DJI	2012-09-17	13553.10	12999.1333333333
DJI	2012-09-18	13564.64	13088.0593333333
DJI	2012-09-19	13577.96	13204.1826666667
DJI	2012-09-20	13596.93	13237.768
DJI	2012-09-21	13579.47	13288.342
DJI	2012-09-24	13558.92	13385.6446666667
DJI	2012-09-25	13457.55	13491.2973333334
DJI	2012-09-26	13413.51	13610.042
DJI	2012-09-27	13485.97	13623.2673333334
DJI	2012-09-28	13437.13	13617.6966666667
DJI	2012-10-01	13515.11	13604.9066666667
DJI	2012-10-02	13482.36	13590.7486666667
DJI	2012-10-03	13494.61	13562.4393333334
DJI	2012-10-04	13575.36	13503.5706666667
DJI	2012-10-05	13610.15	13444.454
DJI	2012-10-08	13583.65	13402.2726666667
DJI	2012-10-09	13473.53	13431.07
DJI	2012-10-10	13344.97	13507.1966666667
DJI	2012-10-11	13326.39	13548.6553333334
DJI	2012-10-12	13328.85	13601.7753333334
DJI	2012-10-15	13424.23	13592.21
DJI	2012-10-16	13551.78	13566.3593333334
DJI	2012-10-17	13557.00	13538.376
DJI	2012-10-18	13548.94	13478.4806666667
DJI	2012-10-19	13343.51	13441.6746666667
DJI	2012-10-22	13345.89	13398.9833333334
DJI	2012-10-23	13102.53	13440.5026666667
DJI	2012-10-24	13077.34	13522.3213333334
DJI	2012-10-25	13103.68	13589.7006666667
DJI	2012-10-26	13107.21	13640.3786666667

In this example, we calculate the intercept for each of the three indices and compare them side-by-side.

SELECT cast(convert(varchar, p1.date_trade, 106) as char(11)) as [trade date],
       p1.price_close as [S&P],
       ROUND(wct.MovingINTERCEPT(p1.price_close, NULL, 9, ROW_NUMBER() over (ORDER 
                 BY p1.date_trade ASC), 1), 2) as [INTERCEPT],
       p2.price_close as [DJIA],
       ROUND(wct.MovingINTERCEPT(p2.price_close, NULL, 9, ROW_NUMBER() over (ORDER 
                 BY p2.date_trade ASC), 2), 2) as [INTERCEPT],
       p3.price_close as [NASDAQ],
       ROUND(wct.MovingINTERCEPT(p3.price_close, NULL, 9, ROW_NUMBER() over (ORDER 
                 BY p3.date_trade ASC), 3), 2) as [INTERCEPT]
FROM #p p1
    JOIN #p p2
        ON p2.date_trade = p1.date_trade
           AND p2.ticker = 'DJI'
    JOIN #p p3
        ON p3.date_trade = p1.date_trade
           AND p3.ticker = 'IXIC'
WHERE p1.ticker = 'GSPC';

This produces the following result.

trade date	S&P	INTERCEPT	DJIA	INTERCEPT	NASDAQ	INTERCEPT
24 Aug 2012	1411.13		13157.97		3069.79
27 Aug 2012	1410.44	3066.39	13124.67	3066.39	3073.19	3066.39
28 Aug 2012	1409.30	3066.02	13102.99	3066.02	3077.14	3066.02
29 Aug 2012	1410.49	3065.79	13107.48	3065.79	3081.19	3065.79
30 Aug 2012	1399.48	3080.25	13000.71	3080.25	3048.71	3080.25
31 Aug 2012	1406.58	3077.85	13090.84	3077.85	3066.96	3077.85
04 Sep 2012	1404.94	3073.87	13035.94	3073.87	3075.06	3073.87
05 Sep 2012	1403.44	3073.23	13047.48	3073.23	3069.27	3073.23
06 Sep 2012	1432.12	3057.96	13292.00	3057.96	3135.81	3057.96
07 Sep 2012	1437.92	3050.07	13306.64	3050.07	3136.42	3050.07
10 Sep 2012	1429.08	3052.27	13254.29	3052.27	3104.02	3052.27
11 Sep 2012	1433.56	3055.06	13323.36	3055.06	3104.53	3055.06
12 Sep 2012	1436.56	3056.14	13333.35	3056.14	3114.31	3056.14
13 Sep 2012	1459.99	3049.50	13539.86	3049.50	3155.83	3049.50
14 Sep 2012	1465.77	3057.37	13593.37	3057.37	3183.95	3057.37
17 Sep 2012	1461.19	3066.80	13553.10	3066.80	3178.67	3066.80
18 Sep 2012	1459.32	3080.07	13564.64	3080.07	3177.80	3080.07
19 Sep 2012	1461.05	3101.93	13577.96	3101.93	3182.62	3101.93
20 Sep 2012	1460.26	3101.62	13596.93	3101.62	3175.96	3101.62
21 Sep 2012	1460.15	3102.91	13579.47	3102.91	3179.96	3102.91
24 Sep 2012	1456.89	3126.05	13558.92	3126.05	3160.78	3126.05
25 Sep 2012	1441.59	3161.36	13457.55	3161.36	3117.73	3161.36
26 Sep 2012	1433.32	3197.78	13413.51	3197.78	3093.70	3197.78
27 Sep 2012	1447.15	3204.87	13485.97	3204.87	3136.60	3204.87
28 Sep 2012	1440.67	3201.63	13437.13	3201.63	3116.23	3201.63
01 Oct 2012	1444.49	3196.88	13515.11	3196.88	3113.53	3196.88
02 Oct 2012	1445.75	3186.90	13482.36	3186.90	3120.04	3186.90
03 Oct 2012	1450.99	3167.77	13494.61	3167.77	3135.23	3167.77
04 Oct 2012	1461.40	3145.75	13575.36	3145.75	3149.46	3145.75
05 Oct 2012	1460.93	3122.75	13610.15	3122.75	3136.19	3122.75
08 Oct 2012	1455.88	3110.55	13583.65	3110.55	3112.35	3110.55
09 Oct 2012	1441.48	3124.67	13473.53	3124.67	3065.02	3124.67
10 Oct 2012	1432.56	3149.15	13344.97	3149.15	3051.78	3149.15
11 Oct 2012	1432.84	3151.28	13326.39	3151.28	3049.41	3151.28
12 Oct 2012	1428.59	3158.17	13328.85	3158.17	3044.11	3158.17
15 Oct 2012	1440.13	3157.49	13424.23	3157.49	3064.18	3157.49
16 Oct 2012	1454.92	3143.09	13551.78	3143.09	3101.17	3143.09
17 Oct 2012	1460.91	3119.75	13557.00	3119.75	3104.12	3119.75
18 Oct 2012	1457.34	3093.95	13548.94	3093.95	3072.87	3093.95
19 Oct 2012	1433.19	3082.68	13343.51	3082.68	3005.62	3082.68
22 Oct 2012	1433.81	3071.57	13345.89	3071.57	3016.96	3071.57
23 Oct 2012	1413.11	3081.48	13102.53	3081.48	2990.46	3081.48
24 Oct 2012	1408.75	3094.36	13077.34	3094.36	2981.70	3094.36
25 Oct 2012	1412.97	3102.78	13103.68	3102.78	2986.12	3102.78
26 Oct 2012	1411.94	3109.10	13107.21	3109.10	2987.95	3109.10

Resources

SQL Server MovingINTERCEPT Function

Description

Syntax

Arguments

@Offset

@RowNum

@Id

Return Type

Remarks

Examples