SQL Server RunningSLOPE Function

Updated 2023-11-14 15:32:11.127000

Description

Use the scalar function RunningSLOPE 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 slope value is calculated from the first row of the resultant table or partition through to the current row. If the column values are presented to the functions out of order, an error message will be generated.

Syntax

SELECT [westclintech].[wct].[RunningSLOPE](
  <@Y, float,>
 ,<@X, float,>
 ,<@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.

@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 RunningSLOPE calculation. @Id allows you to specify multiple RunningSLOPE 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 over a window of x- and y-values use the MovingSLOPE function.

If @RowNum = 1 then RunningSLOPE is NULL.

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. 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(convert(varchar, date_trade, 106) as char(11)) as [trade date],
       price_close as [Closing Price],
       wct.RunningSLOPE(price_close, convert(float, date_trade), ROW_NUMBER() over 
                 (ORDER BY date_trade ASC), NULL) 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	24 Aug 2012	13157.97	NULL
DJI	27 Aug 2012	13124.67	-11.1000000105964
DJI	28 Aug 2012	13102.99	-13.1346153479356
DJI	29 Aug 2012	13107.48	-11.1400000027248
DJI	30 Aug 2012	13000.71	-21.2321698170788
DJI	31 Aug 2012	13090.84	-15.3267027158995
DJI	04 Sep 2012	13035.94	-11.7533266159796
DJI	05 Sep 2012	13047.48	-9.64169642754963
DJI	06 Sep 2012	13292.00	1.39757857186454
DJI	07 Sep 2012	13306.64	7.49219753312476
DJI	10 Sep 2012	13254.29	9.20529712518564
DJI	11 Sep 2012	13323.36	11.5083543761987
DJI	12 Sep 2012	13333.35	12.8233841765938
DJI	13 Sep 2012	13539.86	16.9355971590637
DJI	14 Sep 2012	13593.37	20.2477040831897
DJI	17 Sep 2012	13553.10	21.3690746515016
DJI	18 Sep 2012	13564.64	21.9752322900935
DJI	19 Sep 2012	13577.96	22.2974125057817
DJI	20 Sep 2012	13596.93	22.4961535656962
DJI	21 Sep 2012	13579.47	22.2901224512632
DJI	24 Sep 2012	13558.92	21.3398632977002
DJI	25 Sep 2012	13457.55	19.6836784554935
DJI	26 Sep 2012	13413.51	17.9924654283694
DJI	27 Sep 2012	13485.97	17.0452394014846
DJI	28 Sep 2012	13437.13	15.9137939497098
DJI	01 Oct 2012	13515.11	15.110883375204
DJI	02 Oct 2012	13482.36	14.2332894725561
DJI	03 Oct 2012	13494.61	13.5319004385724
DJI	04 Oct 2012	13575.36	13.2754725347393
DJI	05 Oct 2012	13610.15	13.1577340681447
DJI	08 Oct 2012	13583.65	12.7727484499737
DJI	09 Oct 2012	13473.53	11.9818051795135
DJI	10 Oct 2012	13344.97	10.8255935105077
DJI	11 Oct 2012	13326.39	9.76954818674354
DJI	12 Oct 2012	13328.85	8.86315496858214
DJI	15 Oct 2012	13424.23	8.26427241290096
DJI	16 Oct 2012	13551.78	8.12147923266406
DJI	17 Oct 2012	13557.00	7.99363128510905
DJI	18 Oct 2012	13548.94	7.84342328181691
DJI	19 Oct 2012	13343.51	7.18967234675412
DJI	22 Oct 2012	13345.89	6.54480753721285
DJI	23 Oct 2012	13102.53	5.41612155938403
DJI	24 Oct 2012	13077.34	4.38262391801756
DJI	25 Oct 2012	13103.68	3.53659867569687
DJI	26 Oct 2012	13107.21	2.80180931021162

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 can use the RANK function which will have the effect of equally spacing the x-values.

SELECT ticker,
       cast(convert(varchar, date_trade, 106) as char(11)) as [trade date],
       price_close as [Closing Price],
       wct.RunningSLOPE(
                           price_close,
                           RANK() over (ORDER BY date_trade ASC),
                           ROW_NUMBER() over (ORDER BY date_trade ASC),
                           NULL
                       ) as [SLOPE]
FROM #p
WHERE ticker = 'DJI'
ORDER BY date_trade ASC;

This produces the following result.

ticker	trade date	Closing Price	SLOPE
DJI	24 Aug 2012	13157.97	NULL
DJI	27 Aug 2012	13124.67	-33.3000000000029
DJI	28 Aug 2012	13102.99	-27.4899999999955
DJI	29 Aug 2012	13107.48	-17.3149999999965
DJI	30 Aug 2012	13000.71	-33.1710000000009
DJI	31 Aug 2012	13090.84	-20.086857142856
DJI	04 Sep 2012	13035.94	-19.1439285714284
DJI	05 Sep 2012	13047.48	-16.1940476190454
DJI	06 Sep 2012	13292.00	2.56350000000179
DJI	07 Sep 2012	13306.64	12.7715151515174
DJI	10 Sep 2012	13254.29	15.379454545455
DJI	11 Sep 2012	13323.36	18.9490559440571
DJI	12 Sep 2012	13333.35	20.8111538461552
DJI	13 Sep 2012	13539.86	27.2872967032964
DJI	14 Sep 2012	13593.37	32.1523571428577
DJI	17 Sep 2012	13553.10	33.8101029411773
DJI	18 Sep 2012	13564.64	34.5111029411772
DJI	19 Sep 2012	13577.96	34.6312590299279
DJI	20 Sep 2012	13596.93	34.4699824561407
DJI	21 Sep 2012	13579.47	33.6106240601502
DJI	24 Sep 2012	13558.92	32.2710909090909
DJI	25 Sep 2012	13457.55	29.6804404291367
DJI	26 Sep 2012	13413.51	26.9079841897237
DJI	27 Sep 2012	13485.97	25.2287130434784
DJI	28 Sep 2012	13437.13	23.2377538461538
DJI	01 Oct 2012	13515.11	22.1400957264961
DJI	02 Oct 2012	13482.36	20.8333028083029
DJI	03 Oct 2012	13494.61	19.7238013136295
DJI	04 Oct 2012	13575.36	19.2494187192124
DJI	05 Oct 2012	13610.15	18.9640533926591
DJI	08 Oct 2012	13583.65	18.4554838709681
DJI	09 Oct 2012	13473.53	17.30594941349
DJI	10 Oct 2012	13344.97	15.5730949197866
DJI	11 Oct 2012	13326.39	13.9563636363637
DJI	12 Oct 2012	13328.85	12.5466442577035
DJI	15 Oct 2012	13424.23	11.7323011583015
DJI	16 Oct 2012	13551.78	11.5373067804646
DJI	17 Oct 2012	13557.00	11.3459459459458
DJI	18 Oct 2012	13548.94	11.107914979757
DJI	19 Oct 2012	13343.51	10.1116350844279
DJI	22 Oct 2012	13345.89	9.22727003484329
DJI	23 Oct 2012	13102.53	7.62420630418932
DJI	24 Oct 2012	13077.34	6.13059649652688
DJI	25 Oct 2012	13103.68	4.89284918957001
DJI	26 Oct 2012	13107.21	3.80900922266147
SELECT	cast(conver	(varchar,p1.date_trad	, 106) as char(11)) as
,p1.pr	ce_close as	[S&P]
,p2.pr	ce_close as	[DJIA]
,p3.pr	ce_close as	[NASDAQ]
,ROUND	wct.Running	LOPE(p1.price_close,R	NK() over (ORDER BY p1
,ROUND	wct.Running	LOPE(p2.price_close,R	NK() over (ORDER BY p2
,ROUND	wct.Running	LOPE(p3.price_close,R	NK() over (ORDER BY p3
FROM #	p1
JOIN #	p2
ON p2.	ate_trade =	p1.date_trade
AND p2	ticker = 'D	I'
JOIN #	p3
ON p3.	ate_trade =	p1.date_trade
AND p3	ticker = 'I	IC'
WHERE	1.ticker =	GSPC';

SELECT cast(convert(varchar, p1.date_trade, 106) as char(11)) as [trade date],
       p1.price_close as [S&P],
       p2.price_close as [DJIA],
       p3.price_close as [NASDAQ],
       ROUND(
                wct.RunningSLOPE(
                                    p1.price_close,
                                    RANK() over (ORDER BY p1.date_trade ASC),
                                    ROW_NUMBER() over (ORDER BY p1.date_trade ASC)
                                              ,
                                    1
                                ),
                2
            ) as [S&P slope],
       ROUND(
                wct.RunningSLOPE(
                                    p2.price_close,
                                    RANK() over (ORDER BY p2.date_trade ASC),
                                    ROW_NUMBER() over (ORDER BY p2.date_trade ASC)
                                              ,
                                    2
                                ),
                2
            ) as [DJIA slope],
       ROUND(
                wct.RunningSLOPE(
                                    p3.price_close,
                                    RANK() over (ORDER BY p3.date_trade ASC),
                                    ROW_NUMBER() over (ORDER BY p3.date_trade ASC)
                                              ,
                                    3
                                ),
                2
            ) as [NASDAQ 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	DJIA	NASDAQ	S&P slope	DJIA slope	NASDAQ slope
24 Aug 2012	1411.13	13157.97	3069.79	NULL	NULL	NULL
27 Aug 2012	1410.44	13124.67	3073.19	-0.69	-33.3	3.4
28 Aug 2012	1409.30	13102.99	3077.14	-0.92	-27.49	3.68
29 Aug 2012	1410.49	13107.48	3081.19	-0.31	-17.31	3.82
30 Aug 2012	1399.48	13000.71	3048.71	-2.33	-33.17	-3.42
31 Aug 2012	1406.58	13090.84	3066.96	-1.56	-20.09	-2.39
04 Sep 2012	1404.94	13035.94	3075.06	-1.29	-19.14	-0.9
05 Sep 2012	1403.44	13047.48	3069.27	-1.2	-16.19	-0.68
06 Sep 2012	1432.12	13292.00	3135.81	0.84	2.56	3.9
07 Sep 2012	1437.92	13306.64	3136.42	2.15	12.77	6.05
10 Sep 2012	1429.08	13254.29	3104.02	2.36	15.38	5.48
11 Sep 2012	1433.56	13323.36	3104.53	2.56	18.95	4.96
12 Sep 2012	1436.56	13333.35	3114.31	2.7	20.81	4.8
13 Sep 2012	1459.99	13539.86	3155.83	3.38	27.29	5.75
14 Sep 2012	1465.77	13593.37	3183.95	3.88	32.15	6.93
17 Sep 2012	1461.19	13553.10	3178.67	4.03	33.81	7.45
18 Sep 2012	1459.32	13564.64	3177.80	4.02	34.51	7.64
19 Sep 2012	1461.05	13577.96	3182.62	3.97	34.63	7.72
20 Sep 2012	1460.26	13596.93	3175.96	3.86	34.47	7.56
21 Sep 2012	1460.15	13579.47	3179.96	3.72	33.61	7.39
24 Sep 2012	1456.89	13558.92	3160.78	3.53	32.27	6.92
25 Sep 2012	1441.59	13457.55	3117.73	3.16	29.68	5.97
26 Sep 2012	1433.32	13413.51	3093.70	2.76	26.91	4.92
27 Sep 2012	1447.15	13485.97	3136.60	2.56	25.23	4.5
28 Sep 2012	1440.67	13437.13	3116.23	2.32	23.24	3.93
01 Oct 2012	1444.49	13515.11	3113.53	2.14	22.14	3.44
02 Oct 2012	1445.75	13482.36	3120.04	1.99	20.83	3.07
03 Oct 2012	1450.99	13494.61	3135.23	1.9	19.72	2.86
04 Oct 2012	1461.40	13575.36	3149.46	1.89	19.25	2.77
05 Oct 2012	1460.93	13610.15	3136.19	1.86	18.96	2.59
08 Oct 2012	1455.88	13583.65	3112.35	1.79	18.46	2.29
09 Oct 2012	1441.48	13473.53	3065.02	1.65	17.31	1.76
10 Oct 2012	1432.56	13344.97	3051.78	1.47	15.57	1.24
11 Oct 2012	1432.84	13326.39	3049.41	1.32	13.96	0.78
12 Oct 2012	1428.59	13328.85	3044.11	1.16	12.55	0.38
15 Oct 2012	1440.13	13424.23	3064.18	1.08	11.73	0.12
16 Oct 2012	1454.92	13551.78	3101.17	1.06	11.54	0.06
17 Oct 2012	1460.91	13557.00	3104.12	1.07	11.35	0.02
18 Oct 2012	1457.34	13548.94	3072.87	1.06	11.11	-0.13
19 Oct 2012	1433.19	13343.51	3005.62	0.96	10.11	-0.51
22 Oct 2012	1433.81	13345.89	3016.96	0.87	9.23	-0.79
23 Oct 2012	1413.11	13102.53	2990.46	0.73	7.62	-1.12
24 Oct 2012	1408.75	13077.34	2981.70	0.58	6.13	-1.43
25 Oct 2012	1412.97	13103.68	2986.12	0.47	4.89	-1.68
26 Oct 2012	1411.94	13107.21	2987.95	0.36	3.81	-1.89

Resources

SQL Server RunningSLOPE Function

Description

Syntax

Arguments

@Y

@X

@RowNum

@Id

Return Type

Remarks

Examples