DWMA
Updated: 31 Oct 2012
Use the scalar function DWMA function to calculate a daily weighted moving average across multiple rows within a resultant table, without the need for a self-join. DWMA allows you to define the size of a window and calculates the DWMA for each row in the window. The following equation describes the DWMA calculation:
Where:
n is the window size
x0 is the most current row in the window
If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[DWMA](
<@Val, float,>
,<@Days, int,>
,<@Lag, int,>
,<@RowNum, int,>
,<@Id, tinyint,>)
GO
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.
@Days
the size of the window, including the current row. @Days is the number or rows, including the current row, in the window. @Days is an expression of type int or of a type that can be implicitly converted to int.
@Lag
Indicates that the daily weighted moving average is to be displayed in the current row (@Lag = 0) or the subsequent row (@Lag = 1). @Lag is an expression of type int or of a type that can be implicitly converted to int.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the DWMA calculation. @Id allows you to specify multiple DWMA 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.
· 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 5-day moving average for the closing price of MSFT. 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_adj_close money,
PRIMARY KEY (ticker, date_trade)
)
--Put data into the table
INSERT INTO #p VALUES ('MSFT','2012-10-31',28.54)
INSERT INTO #p VALUES ('MSFT','2012-10-26',28.21)
INSERT INTO #p VALUES ('MSFT','2012-10-25',27.88)
INSERT INTO #p VALUES ('MSFT','2012-10-24',27.9)
INSERT INTO #p VALUES ('MSFT','2012-10-23',28.05)
INSERT INTO #p VALUES ('MSFT','2012-10-22',28)
INSERT INTO #p VALUES ('MSFT','2012-10-19',28.64)
INSERT INTO #p VALUES ('MSFT','2012-10-18',29.5)
INSERT INTO #p VALUES ('MSFT','2012-10-17',29.59)
INSERT INTO #p VALUES ('MSFT','2012-10-16',29.49)
INSERT INTO #p VALUES ('MSFT','2012-10-15',29.51)
INSERT INTO #p VALUES ('MSFT','2012-10-12',29.2)
INSERT INTO #p VALUES ('MSFT','2012-10-11',28.95)
INSERT INTO #p VALUES ('MSFT','2012-10-10',28.98)
INSERT INTO #p VALUES ('MSFT','2012-10-09',29.28)
INSERT INTO #p VALUES ('MSFT','2012-10-08',29.78)
INSERT INTO #p VALUES ('MSFT','2012-10-05',29.85)
INSERT INTO #p VALUES ('MSFT','2012-10-04',30.03)
INSERT INTO #p VALUES ('MSFT','2012-10-03',29.86)
INSERT INTO #p VALUES ('MSFT','2012-10-02',29.66)
INSERT INTO #p VALUES ('MSFT','2012-10-01',29.49)
INSERT INTO #p VALUES ('MSFT','2012-09-28',29.76)
INSERT INTO #p VALUES ('MSFT','2012-09-27',30.16)
INSERT INTO #p VALUES ('MSFT','2012-09-26',30.17)
INSERT INTO #p VALUES ('MSFT','2012-09-25',30.39)
INSERT INTO #p VALUES ('MSFT','2012-09-24',30.78)
INSERT INTO #p VALUES ('MSFT','2012-09-21',31.19)
INSERT INTO #p VALUES ('MSFT','2012-09-20',31.45)
INSERT INTO #p VALUES ('MSFT','2012-09-19',31.05)
INSERT INTO #p VALUES ('MSFT','2012-09-18',31.18)
INSERT INTO #p VALUES ('MSFT','2012-09-17',31.21)
INSERT INTO #p VALUES ('MSFT','2012-09-14',31.21)
INSERT INTO #p VALUES ('MSFT','2012-09-13',30.94)
INSERT INTO #p VALUES ('MSFT','2012-09-12',30.78)
INSERT INTO #p VALUES ('MSFT','2012-09-11',30.79)
INSERT INTO #p VALUES ('MSFT','2012-09-10',30.72)
INSERT INTO #p VALUES ('MSFT','2012-09-07',30.95)
INSERT INTO #p VALUES ('MSFT','2012-09-06',31.35)
INSERT INTO #p VALUES ('MSFT','2012-09-05',30.39)
INSERT INTO #p VALUES ('MSFT','2012-09-04',30.39)
INSERT INTO #p VALUES ('MSFT','2012-08-31',30.82)
INSERT INTO #p VALUES ('MSFT','2012-08-30',30.32)
INSERT INTO #p VALUES ('MSFT','2012-08-29',30.65)
INSERT INTO #p VALUES ('MSFT','2012-08-28',30.63)
INSERT INTO #p VALUES ('MSFT','2012-08-27',30.69)
INSERT INTO #p VALUES ('MSFT','2012-08-24',30.56)
INSERT INTO #p VALUES ('MSFT','2012-08-23',30.26)
INSERT INTO #p VALUES ('MSFT','2012-08-22',30.54)
INSERT INTO #p VALUES ('MSFT','2012-08-21',30.8)
INSERT INTO #p VALUES ('MSFT','2012-08-20',30.74)
INSERT INTO #p VALUES ('MSFT','2012-08-17',30.9)
INSERT INTO #p VALUES ('MSFT','2012-08-16',30.78)
INSERT INTO #p VALUES ('MSFT','2012-08-15',30.2)
INSERT INTO #p VALUES ('MSFT','2012-08-14',30.13)
INSERT INTO #p VALUES ('MSFT','2012-08-13',30.19)
INSERT INTO #p VALUES ('MSFT','2012-08-10',30.22)
INSERT INTO #p VALUES ('MSFT','2012-08-09',30.3)
INSERT INTO #p VALUES ('MSFT','2012-08-08',30.13)
INSERT INTO #p VALUES ('MSFT','2012-08-07',30.06)
INSERT INTO #p VALUES ('MSFT','2012-08-06',29.75)
INSERT INTO #p VALUES ('MSFT','2012-08-03',29.55)
INSERT INTO #p VALUES ('MSFT','2012-08-02',29)
INSERT INTO #p VALUES ('MSFT','2012-08-01',29.22)
INSERT INTO #p VALUES ('MSFT','2012-07-31',29.28)
--Calculate the 5-day weighted moving average
SELECT cast(date_trade as date) as date_trade
,price_adj_close
,wct.DWMA(price_adj_close,5,0,ROW_NUMBER() OVER (ORDER BY date_trade),NULL) as [5-day]
FROM #p
ORDER BY 1
--Clean up
DROP TABLE #p
This produces the following result.
date_trade price_adj_close 5-day
---------- --------------------- ----------------------
2012-07-31 29.28 NULL
2012-08-01 29.22 NULL
2012-08-02 29.00 NULL
2012-08-03 29.55 NULL
2012-08-06 29.75 29.4446666666667
2012-08-07 30.06 29.678
2012-08-08 30.13 29.8826666666667
2012-08-09 30.30 30.0833333333333
2012-08-10 30.22 30.1706666666667
2012-08-13 30.19 30.2033333333333
2012-08-14 30.13 30.1866666666667
2012-08-15 30.20 30.1886666666667
2012-08-16 30.78 30.3793333333333
2012-08-17 30.90 30.578
2012-08-20 30.74 30.678
2012-08-21 30.80 30.7613333333333
2012-08-22 30.54 30.7133333333333
2012-08-23 30.26 30.5493333333333
2012-08-24 30.56 30.52
2012-08-27 30.69 30.5566666666667
2012-08-28 30.63 30.5766666666667
2012-08-29 30.65 30.6146666666667
2012-08-30 30.32 30.5353333333333
2012-08-31 30.82 30.6186666666667
2012-09-04 30.39 30.5413333333333
2012-09-05 30.39 30.484
2012-09-06 31.35 30.7626666666667
2012-09-07 30.95 30.8613333333333
2012-09-10 30.72 30.8413333333333
2012-09-11 30.79 30.8513333333333
2012-09-12 30.78 30.8313333333333
2012-09-13 30.94 30.8386666666667
2012-09-14 31.21 30.9633333333333
2012-09-17 31.21 31.0706666666667
2012-09-18 31.18 31.1353333333333
2012-09-19 31.05 31.1306666666667
2012-09-20 31.45 31.2413333333333
2012-09-21 31.19 31.2313333333333
2012-09-24 30.78 31.086
2012-09-25 30.39 30.8393333333333
2012-09-26 30.17 30.572
2012-09-27 30.16 30.36
2012-09-28 29.76 30.1006666666667
2012-10-01 29.49 29.8466666666667
2012-10-02 29.66 29.7353333333333
2012-10-03 29.86 29.7393333333333
2012-10-04 30.03 29.8206666666667
2012-10-05 29.85 29.8506666666667
2012-10-08 29.78 29.8513333333333
2012-10-09 29.28 29.666
2012-10-10 28.98 29.406
2012-10-11 28.95 29.1946666666667
2012-10-12 29.20 29.1386666666667
2012-10-15 29.51 29.2293333333333
2012-10-16 29.49 29.3313333333333
2012-10-17 29.59 29.4526666666667
2012-10-18 29.50 29.5033333333333
2012-10-19 28.64 29.2306666666667
2012-10-22 28.00 28.782
2012-10-23 28.05 28.4506666666667
2012-10-24 27.90 28.1653333333333
2012-10-25 27.88 27.986
2012-10-26 28.21 28.0246666666666
2012-10-31 28.54 28.202
In this example, we use the LAG function to shoe how the DWMA calculation works.
SELECT cast(date_trade as date) as date_trade
,price_adj_close
,(
wct.LAG(price_adj_close * 1,5,NULL,ROW_NUMBER() OVER (ORDER BY date_trade),5) +
wct.LAG(price_adj_close * 2,4,NULL,ROW_NUMBER() OVER (ORDER BY date_trade),4) +
wct.LAG(price_adj_close * 3,3,NULL,ROW_NUMBER() OVER (ORDER BY date_trade),3) +
wct.LAG(price_adj_close * 4,2,NULL,ROW_NUMBER() OVER (ORDER BY date_trade),2) +
wct.LAG(price_adj_close * 5,1,NULL,ROW_NUMBER() OVER (ORDER BY date_trade),1)) / 15 as [LAG 5-day ]
,wct.DWMA(price_adj_close,5,1,ROW_NUMBER() OVER (ORDER BY date_trade),NULL) as [DWMA 5-day]
FROM #p
This produces the following result.
date_trade price_adj_close LAG 5-day DWMA 5-day
---------- --------------------- ---------------------- ----------------------
2012-07-31 29.28 NULL NULL
2012-08-01 29.22 NULL NULL
2012-08-02 29.00 NULL NULL
2012-08-03 29.55 NULL NULL
2012-08-06 29.75 NULL NULL
2012-08-07 30.06 29.4446666666667 29.4446666666667
2012-08-08 30.13 29.678 29.678
2012-08-09 30.30 29.8826666666667 29.8826666666667
2012-08-10 30.22 30.0833333333333 30.0833333333333
2012-08-13 30.19 30.1706666666667 30.1706666666667
2012-08-14 30.13 30.2033333333333 30.2033333333333
2012-08-15 30.20 30.1866666666667 30.1866666666667
2012-08-16 30.78 30.1886666666667 30.1886666666667
2012-08-17 30.90 30.3793333333333 30.3793333333333
2012-08-20 30.74 30.578 30.578
2012-08-21 30.80 30.678 30.678
2012-08-22 30.54 30.7613333333333 30.7613333333333
2012-08-23 30.26 30.7133333333333 30.7133333333333
2012-08-24 30.56 30.5493333333333 30.5493333333333
2012-08-27 30.69 30.52 30.52
2012-08-28 30.63 30.5566666666667 30.5566666666667
2012-08-29 30.65 30.5766666666667 30.5766666666667
2012-08-30 30.32 30.6146666666667 30.6146666666667
2012-08-31 30.82 30.5353333333333 30.5353333333333
2012-09-04 30.39 30.6186666666667 30.6186666666667
2012-09-05 30.39 30.5413333333333 30.5413333333333
2012-09-06 31.35 30.484 30.484
2012-09-07 30.95 30.7626666666667 30.7626666666667
2012-09-10 30.72 30.8613333333333 30.8613333333333
2012-09-11 30.79 30.8413333333333 30.8413333333333
2012-09-12 30.78 30.8513333333333 30.8513333333333
2012-09-13 30.94 30.8313333333333 30.8313333333333
2012-09-14 31.21 30.8386666666667 30.8386666666667
2012-09-17 31.21 30.9633333333333 30.9633333333333
2012-09-18 31.18 31.0706666666667 31.0706666666667
2012-09-19 31.05 31.1353333333333 31.1353333333333
2012-09-20 31.45 31.1306666666667 31.1306666666667
2012-09-21 31.19 31.2413333333333 31.2413333333333
2012-09-24 30.78 31.2313333333333 31.2313333333333
2012-09-25 30.39 31.086 31.086
2012-09-26 30.17 30.8393333333333 30.8393333333333
2012-09-27 30.16 30.572 30.572
2012-09-28 29.76 30.36 30.36
2012-10-01 29.49 30.1006666666667 30.1006666666667
2012-10-02 29.66 29.8466666666667 29.8466666666667
2012-10-03 29.86 29.7353333333333 29.7353333333333
2012-10-04 30.03 29.7393333333333 29.7393333333333
2012-10-05 29.85 29.8206666666667 29.8206666666667
2012-10-08 29.78 29.8506666666667 29.8506666666667
2012-10-09 29.28 29.8513333333333 29.8513333333333
2012-10-10 28.98 29.666 29.666
2012-10-11 28.95 29.406 29.406
2012-10-12 29.20 29.1946666666667 29.1946666666667
2012-10-15 29.51 29.1386666666667 29.1386666666667
2012-10-16 29.49 29.2293333333333 29.2293333333333
2012-10-17 29.59 29.3313333333333 29.3313333333333
2012-10-18 29.50 29.4526666666667 29.4526666666667
2012-10-19 28.64 29.5033333333333 29.5033333333333
2012-10-22 28.00 29.2306666666667 29.2306666666667
2012-10-23 28.05 28.782 28.782
2012-10-24 27.90 28.4506666666667 28.4506666666667
2012-10-25 27.88 28.1653333333333 28.1653333333333
2012-10-26 28.21 27.986 27.986
2012-10-31 28.54 28.0246666666667 28.0246666666666