Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running SLOPE function


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
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service