SQL Server MovingSLOPE Function

Updated 2023-11-13 21:45:11.567000

Description

Use the scalar function MovingSLOPE to calculate the slope 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].[MovingSLOPE](
  <@Y, float,>
 ,<@X, float,>
 ,<@Offset, int,>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>)

Arguments

@Y

the y-value passed into the function. @Y is an expression of type float or of a type that can be implicitly converted to float.

@X

the x-value passed into the function. @X is an expression of type float or of a type that can be implicitly converted to float.

@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 MovingSLOPE calculation. @Id allows you to specify multiple MovingSLOPE 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 slope from the first row of a dataset or partition use the RunningSLOPE function.

If @RowNum = 1 then MovingSLOPE 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 slope value for a set of x- and y-values, use the SLOPE 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 slope, 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 SLOPE for DJI
SELECT ticker,
       cast(date_trade as date) as [trade date],
       price_close as [Closing Price],
       cast(wct.MovingSLOPE(
                               price_close,
                               convert(float, date_trade),
                               9,
                               ROW_NUMBER() over (ORDER BY date_trade ASC),
                               NULL
                           ) as money) as [SLOPE]
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	SLOPE
DJI	2012-08-24	13157.97	NULL
DJI	2012-08-27	13124.67	-11.10
DJI	2012-08-28	13102.99	-13.1346
DJI	2012-08-29	13107.48	-11.14
DJI	2012-08-30	13000.71	-21.2322
DJI	2012-08-31	13090.84	-15.3267
DJI	2012-09-04	13035.94	-11.7533
DJI	2012-09-05	13047.48	-9.6417
DJI	2012-09-06	13292.00	1.3976
DJI	2012-09-07	13306.64	7.4922
DJI	2012-09-10	13254.29	13.49
DJI	2012-09-11	13323.36	18.1904
DJI	2012-09-12	13333.35	21.4768
DJI	2012-09-13	13539.86	30.614
DJI	2012-09-14	13593.37	35.9727
DJI	2012-09-17	13553.10	40.2777
DJI	2012-09-18	13564.64	34.6173
DJI	2012-09-19	13577.96	27.0955
DJI	2012-09-20	13596.93	27.8024
DJI	2012-09-21	13579.47	27.2087
DJI	2012-09-24	13558.92	16.4785
DJI	2012-09-25	13457.55	4.7364
DJI	2012-09-26	13413.51	-8.4681
DJI	2012-09-27	13485.97	-11.283
DJI	2012-09-28	13437.13	-13.6611
DJI	2012-10-01	13515.11	-10.8446
DJI	2012-10-02	13482.36	-9.6347
DJI	2012-10-03	13494.61	-7.2527
DJI	2012-10-04	13575.36	-0.4708
DJI	2012-10-05	13610.15	8.4471
DJI	2012-10-08	13583.65	13.1063
DJI	2012-10-09	13473.53	9.0655
DJI	2012-10-10	13344.97	-0.83
DJI	2012-10-11	13326.39	-7.8113
DJI	2012-10-12	13328.85	-17.8552
DJI	2012-10-15	13424.23	-15.8098
DJI	2012-10-16	13551.78	-10.7361
DJI	2012-10-17	13557.00	-6.6551
DJI	2012-10-18	13548.94	0.2482
DJI	2012-10-19	13343.51	3.2788
DJI	2012-10-22	13345.89	4.0888
DJI	2012-10-23	13102.53	-6.3894
DJI	2012-10-24	13077.34	-19.3378
DJI	2012-10-25	13103.68	-31.5279
DJI	2012-10-26	13107.21	-45.9742

Notice that in the previous example that the interval between dates is unequal because there are no prices for the weekends and holidays. To eliminate that, we simple enter NULL for the x-values, and the x-values will be equally spaced in the range from 1 to @Offset + 1.

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

This produces the following result.

ticker	trade date	Closing Price	SLOPE
DJI	2012-08-24	13157.97	NULL
DJI	2012-08-27	13124.67	-33.30
DJI	2012-08-28	13102.99	-27.49
DJI	2012-08-29	13107.48	-17.315
DJI	2012-08-30	13000.71	-33.171
DJI	2012-08-31	13090.84	-20.0869
DJI	2012-09-04	13035.94	-19.1439
DJI	2012-09-05	13047.48	-16.194
DJI	2012-09-06	13292.00	2.5635
DJI	2012-09-07	13306.64	12.7715
DJI	2012-09-10	13254.29	21.819
DJI	2012-09-11	13323.36	31.2465
DJI	2012-09-12	13333.35	37.3652
DJI	2012-09-13	13539.86	52.2551
DJI	2012-09-14	13593.37	57.7048
DJI	2012-09-17	13553.10	59.7828
DJI	2012-09-18	13564.64	53.2272
DJI	2012-09-19	13577.96	41.759
DJI	2012-09-20	13596.93	41.1967
DJI	2012-09-21	13579.47	36.962
DJI	2012-09-24	13558.92	24.8093
DJI	2012-09-25	13457.55	8.0396
DJI	2012-09-26	13413.51	-12.0929
DJI	2012-09-27	13485.97	-15.4773
DJI	2012-09-28	13437.13	-17.3052
DJI	2012-10-01	13515.11	-15.6705
DJI	2012-10-02	13482.36	-14.5923
DJI	2012-10-03	13494.61	-10.9606
DJI	2012-10-04	13575.36	-0.6494
DJI	2012-10-05	13610.15	10.6569
DJI	2012-10-08	13583.65	18.7759
DJI	2012-10-09	13473.53	13.8305
DJI	2012-10-10	13344.97	-1.2568
DJI	2012-10-11	13326.39	-11.6962
DJI	2012-10-12	13328.85	-23.3232
DJI	2012-10-15	13424.23	-23.2364
DJI	2012-10-16	13551.78	-17.2741
DJI	2012-10-17	13557.00	-11.0518
DJI	2012-10-18	13548.94	-0.6421
DJI	2012-10-19	13343.51	1.2019
DJI	2012-10-22	13345.89	4.641
DJI	2012-10-23	13102.53	-9.6534
DJI	2012-10-24	13077.34	-29.3955
DJI	2012-10-25	13103.68	-45.6956
DJI	2012-10-26	13107.21	-58.9396

In this example we will calculate the SLOPE for all three indices and display the values 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.MovingSLOPE(p1.price_close, NULL, 9, ROW_NUMBER() over (ORDER BY 
                 p1.date_trade ASC), 1), 2) as [SLOPE],
       p2.price_close as [DJIA],
       ROUND(wct.MovingSLOPE(p2.price_close, NULL, 9, ROW_NUMBER() over (ORDER BY 
                 p2.date_trade ASC), 2), 2) as [SLOPE],
       p3.price_close as [NASDAQ],
       ROUND(wct.MovingSLOPE(p3.price_close, NULL, 9, ROW_NUMBER() over (ORDER BY 
                 p3.date_trade ASC), 3), 2) as [SLOPE]
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	SLOPE	DJIA	SLOPE	NASDAQ	SLOPE
24 Aug 2012	1411.13		13157.97		3069.79
27 Aug 2012	1410.44	3.40	13124.67	3.40	3073.19	3.40
28 Aug 2012	1409.30	3.68	13102.99	3.68	3077.14	3.68
29 Aug 2012	1410.49	3.82	13107.48	3.82	3081.19	3.82
30 Aug 2012	1399.48	-3.42	13000.71	-3.42	3048.71	-3.42
31 Aug 2012	1406.58	-2.39	13090.84	-2.39	3066.96	-2.39
04 Sep 2012	1404.94	-.90	13035.94	-.90	3075.06	-.90
05 Sep 2012	1403.44	-.68	13047.48	-.68	3069.27	-.68
06 Sep 2012	1432.12	3.90	13292.00	3.90	3135.81	3.90
07 Sep 2012	1437.92	6.05	13306.64	6.05	3136.42	6.05
10 Sep 2012	1429.08	6.27	13254.29	6.27	3104.02	6.27
11 Sep 2012	1433.56	6.34	13323.36	6.34	3104.53	6.34
12 Sep 2012	1436.56	6.82	13333.35	6.82	3114.31	6.82
13 Sep 2012	1459.99	9.38	13539.86	9.38	3155.83	9.38
14 Sep 2012	1465.77	10.41	13593.37	10.41	3183.95	10.41
17 Sep 2012	1461.19	10.72	13553.10	10.72	3178.67	10.72
18 Sep 2012	1459.32	10.18	13564.64	10.18	3177.80	10.18
19 Sep 2012	1461.05	8.27	13577.96	8.27	3182.62	8.27
20 Sep 2012	1460.26	9.05	13596.93	9.05	3175.96	9.05
21 Sep 2012	1460.15	9.61	13579.47	9.61	3179.96	9.61
24 Sep 2012	1456.89	6.43	13558.92	6.43	3160.78	6.43
25 Sep 2012	1441.59	.26	13457.55	.26	3117.73	.26
26 Sep 2012	1433.32	-6.74	13413.51	-6.74	3093.70	-6.74
27 Sep 2012	1447.15	-8.38	13485.97	-8.38	3136.60	-8.38
28 Sep 2012	1440.67	-9.02	13437.13	-9.02	3116.23	-9.02
01 Oct 2012	1444.49	-9.34	13515.11	-9.34	3113.53	-9.34
02 Oct 2012	1445.75	-8.58	13482.36	-8.58	3120.04	-8.58
03 Oct 2012	1450.99	-5.96	13494.61	-5.96	3135.23	-5.96
04 Oct 2012	1461.40	-2.44	13575.36	-2.44	3149.46	-2.44
05 Oct 2012	1460.93	.95	13610.15	.95	3136.19	.95
08 Oct 2012	1455.88	2.28	13583.65	2.28	3112.35	2.28
09 Oct 2012	1441.48	-1.24	13473.53	-1.24	3065.02	-1.24
10 Oct 2012	1432.56	-6.45	13344.97	-6.45	3051.78	-6.45
11 Oct 2012	1432.84	-8.43	13326.39	-8.43	3049.41	-8.43
12 Oct 2012	1428.59	-10.99	13328.85	-10.99	3044.11	-10.99
15 Oct 2012	1440.13	-11.77	13424.23	-11.77	3064.18	-11.77
16 Oct 2012	1454.92	-9.49	13551.78	-9.49	3101.17	-9.49
17 Oct 2012	1460.91	-5.81	13557.00	-5.81	3104.12	-5.81
18 Oct 2012	1457.34	-2.51	13548.94	-2.51	3072.87	-2.51
19 Oct 2012	1433.19	-2.84	13343.51	-2.84	3005.62	-2.84
22 Oct 2012	1433.81	-2.55	13345.89	-2.55	3016.96	-2.55
23 Oct 2012	1413.11	-5.71	13102.53	-5.71	2990.46	-5.71
24 Oct 2012	1408.75	-9.33	13077.34	-9.33	2981.70	-9.33
25 Oct 2012	1412.97	-12.01	13103.68	-12.01	2986.12	-12.01
26 Oct 2012	1411.94	-14.18	13107.21	-14.18	2987.95	-14.18

Resources

SQL Server MovingSLOPE Function

Description

Syntax

Arguments

@Y

@X

@Offset

@RowNum

@Id

Return Type

Remarks

Examples