SQL Server LAG Function
Updated 2023-11-13 15:46:23.883000
Description
Use the scalar function LAG function to obtain access to multiple rows within a resultant table, without the need for a self-join. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [westclintech].[wct].[LAG](
<@Val, float,>
,<@Offset, int,>
,<@DefaultValue, float,>
,<@RowNum, int,>
,<@Id, tinyint,>)
Arguments
@Val
the value passed into the function. @Val is an expression of type float or of a type that can be implicitly converted to float.
@Offset
the size of the window, including the current row. @Offset is an expression of type int or of a type that can be implicitly converted to int.
@DefaultValue
the value to be used when @Val is NULL. @DefaultValue 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 LAG calculation. @Id allows you to specify multiple LAG 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.
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 percentage change in closing price 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
('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 day-over-day percentage change
--in the closing price of GSPC
SELECT ticker,
cast(convert(varchar, date_trade, 106) as char(11)) as [trade date],
price_close as [Closing Price],
price_close / wct.LAG(price_close, 1, NULL, ROW_NUMBER() over (ORDER BY
date_trade ASC), NULL) - 1 as [Pct. Change]
FROM #p
WHERE ticker = 'GSPC'
ORDER BY date_trade ASC;
--Clean up
DROP TABLE #p;
This produces the following result.
| ticker | trade date | Closing Price | Pct. Change |
|---|---|---|---|
| GSPC | 27 Aug 2012 | 1410.44 | NULL |
| GSPC | 28 Aug 2012 | 1409.30 | -0.000808258415813556 |
| GSPC | 29 Aug 2012 | 1410.49 | 0.000844390832328124 |
| GSPC | 30 Aug 2012 | 1399.48 | -0.0078057979850974 |
| GSPC | 31 Aug 2012 | 1406.58 | 0.00507331294480795 |
| GSPC | 04 Sep 2012 | 1404.94 | -0.00116594861294761 |
| GSPC | 05 Sep 2012 | 1403.44 | -0.00106766125243785 |
| GSPC | 06 Sep 2012 | 1432.12 | 0.0204355013395656 |
| GSPC | 07 Sep 2012 | 1437.92 | 0.00404993994916647 |
| GSPC | 10 Sep 2012 | 1429.08 | -0.00614776899966629 |
| GSPC | 11 Sep 2012 | 1433.56 | 0.00313488398130257 |
| GSPC | 12 Sep 2012 | 1436.56 | 0.00209269231842413 |
| GSPC | 13 Sep 2012 | 1459.99 | 0.0163097956228768 |
| GSPC | 14 Sep 2012 | 1465.77 | 0.00395893122555635 |
| GSPC | 17 Sep 2012 | 1461.19 | -0.0031246375625098 |
| GSPC | 18 Sep 2012 | 1459.32 | -0.00127977881042174 |
| GSPC | 19 Sep 2012 | 1461.05 | 0.00118548364991922 |
| GSPC | 20 Sep 2012 | 1460.26 | -0.000540707025769138 |
| GSPC | 21 Sep 2012 | 1460.15 | -7.53290509908355E-05 |
| GSPC | 24 Sep 2012 | 1456.89 | -0.00223264733075368 |
| GSPC | 25 Sep 2012 | 1441.59 | -0.0105018223750594 |
| GSPC | 26 Sep 2012 | 1433.32 | -0.00573672125916525 |
| GSPC | 27 Sep 2012 | 1447.15 | 0.00964892696676256 |
| GSPC | 28 Sep 2012 | 1440.67 | -0.00447776664478461 |
| GSPC | 01 Oct 2012 | 1444.49 | 0.00265154407324375 |
| GSPC | 02 Oct 2012 | 1445.75 | 0.00087228018193275 |
| GSPC | 03 Oct 2012 | 1450.99 | 0.00362441639287558 |
| GSPC | 04 Oct 2012 | 1461.40 | 0.00717441195321822 |
| GSPC | 05 Oct 2012 | 1460.93 | -0.000321609415628865 |
| GSPC | 08 Oct 2012 | 1455.88 | -0.00345670223761574 |
| GSPC | 09 Oct 2012 | 1441.48 | -0.00989092507624256 |
| GSPC | 10 Oct 2012 | 1432.56 | -0.00618808446874053 |
| GSPC | 11 Oct 2012 | 1432.84 | 0.000195454291617825 |
| GSPC | 12 Oct 2012 | 1428.59 | -0.00296613718209993 |
| GSPC | 15 Oct 2012 | 1440.13 | 0.00807789498736522 |
| GSPC | 16 Oct 2012 | 1454.92 | 0.0102699061890246 |
| GSPC | 17 Oct 2012 | 1460.91 | 0.00411706485579955 |
| GSPC | 18 Oct 2012 | 1457.34 | -0.00244368236236325 |
| GSPC | 19 Oct 2012 | 1433.19 | -0.0165712874140557 |
| GSPC | 22 Oct 2012 | 1433.81 | 0.000432601399674715 |
| GSPC | 23 Oct 2012 | 1413.11 | -0.014437059303534 |
| GSPC | 24 Oct 2012 | 1408.75 | -0.00308539321071954 |
| GSPC | 25 Oct 2012 | 1412.97 | 0.00299556344276852 |
| GSPC | 26 Oct 2012 | 1411.94 | -0.000728960982894211 |
In this example we calculate the 5-day moving average for 'IXIC' , even though it would be much simpler to use the DWMA function.
SELECT ticker,
cast(convert(varchar, date_trade, 106) as char(11)) as [trade date],
price_close as [Closing Price],
(wct.LAG(price_close * 1, 5, NULL, ROW_NUMBER() over (ORDER BY date_trade
ASC), 5)
+ wct.LAG(price_close * 2, 4, NULL, ROW_NUMBER() over (ORDER BY date_trade
ASC), 4)
+ wct.LAG(price_close * 3, 3, NULL, ROW_NUMBER() over (ORDER BY date_trade
ASC), 3)
+ wct.LAG(price_close * 4, 2, NULL, ROW_NUMBER() over (ORDER BY date_trade
ASC), 2)
+ wct.LAG(price_close * 5, 1, NULL, ROW_NUMBER() over (ORDER BY date_trade
ASC), 1)
) / 15 as [5-day Moving Avg.]
FROM #p
WHERE ticker = 'IXIC';
This produces the following result.
| ticker | trade date | Closing Price | 5-day Moving Avg. |
|---|---|---|---|
| IXIC | 24 Aug 2012 | 3069.79 | NULL |
| IXIC | 27 Aug 2012 | 3073.19 | NULL |
| IXIC | 28 Aug 2012 | 3077.14 | NULL |
| IXIC | 29 Aug 2012 | 3081.19 | NULL |
| IXIC | 30 Aug 2012 | 3048.71 | NULL |
| IXIC | 31 Aug 2012 | 3066.96 | 3067.72666666667 |
| IXIC | 04 Sep 2012 | 3075.06 | 3066.712 |
| IXIC | 05 Sep 2012 | 3069.27 | 3068.586 |
| IXIC | 06 Sep 2012 | 3135.81 | 3068.40533333333 |
| IXIC | 07 Sep 2012 | 3136.42 | 3090.92933333333 |
| IXIC | 10 Sep 2012 | 3104.02 | 3110.01533333333 |
| IXIC | 11 Sep 2012 | 3104.53 | 3112.454 |
| IXIC | 12 Sep 2012 | 3114.31 | 3112.592 |
| IXIC | 13 Sep 2012 | 3155.83 | 3114.02533333333 |
| IXIC | 14 Sep 2012 | 3183.95 | 3126.296 |
| IXIC | 17 Sep 2012 | 3178.67 | 3146.60533333333 |
| IXIC | 18 Sep 2012 | 3177.80 | 3161.986 |
| IXIC | 19 Sep 2012 | 3182.62 | 3172.1 |
| IXIC | 20 Sep 2012 | 3175.96 | 3178.936 |
| IXIC | 21 Sep 2012 | 3179.96 | 3178.998 |
| IXIC | 24 Sep 2012 | 3160.78 | 3179.05133333333 |
| IXIC | 25 Sep 2012 | 3117.73 | 3172.97733333333 |
| IXIC | 26 Sep 2012 | 3093.70 | 3153.746 |
| IXIC | 27 Sep 2012 | 3136.60 | 3130.50933333333 |
| IXIC | 28 Sep 2012 | 3116.23 | 3127.50066666667 |
| IXIC | 01 Oct 2012 | 3113.53 | 3120.326 |
| IXIC | 02 Oct 2012 | 3120.04 | 3116.5 |
| IXIC | 03 Oct 2012 | 3135.23 | 3117.994 |
| IXIC | 04 Oct 2012 | 3149.46 | 3124.39733333333 |
| IXIC | 05 Oct 2012 | 3136.19 | 3132.77533333333 |
| IXIC | 08 Oct 2012 | 3112.35 | 3135.87266666667 |
| IXIC | 09 Oct 2012 | 3065.02 | 3129.69266666667 |
| IXIC | 10 Oct 2012 | 3051.78 | 3107.81466666667 |
| IXIC | 11 Oct 2012 | 3049.41 | 3085.19133333333 |
| IXIC | 12 Oct 2012 | 3044.11 | 3067.34133333333 |
| IXIC | 15 Oct 2012 | 3064.18 | 3054.39466666667 |
| IXIC | 16 Oct 2012 | 3101.17 | 3054.27666666667 |
| IXIC | 17 Oct 2012 | 3104.12 | 3069.7 |
| IXIC | 18 Oct 2012 | 3072.87 | 3083.69666666667 |
| IXIC | 19 Oct 2012 | 3005.62 | 3083.78733333333 |
| IXIC | 22 Oct 2012 | 3016.96 | 3059.89733333333 |
| IXIC | 23 Oct 2012 | 2990.46 | 3042.35333333333 |
| IXIC | 24 Oct 2012 | 2981.70 | 3019.124 |
| IXIC | 25 Oct 2012 | 2986.12 | 3000.35533333333 |
| IXIC | 26 Oct 2012 | 2987.95 | 2991.22133333333 |