RunningSLOPE
Updated: 31 Oct 2012
Use the RunningSLOPE function 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 [Example].[wct].[RunningSLOPE](
<@Y, float,>
,<@X, float,>
,<@RowNum, int,>
,<@Id, tinyint,>)
GO
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.
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.
Example
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 folowing 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(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