DEMA

Updated 2023-11-10 21:51:52.187000

Syntax

SELECT [westclintech].[wct].[DEMA](
  <@Val, float,>
 ,<@Days, int,>
 ,<@Lag, int,>
 ,<@FirstValue, nvarchar(4000),>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>)

Description

Use DEMA to calculate the daily exponential weighted moving average of column values in an ordered resultant table, without the need for a self-join. DEMA allows you to define the size of a window calculates the DEMA for each row in the window. If the column values are presented to the functions out of order, an error message will be generated.

Arguments

@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.

@Id

a unique identifier for the DEMA calculation. @Id allows you to specify multiple DEMA calculation s within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.

@FirstValue

Identifies whether the first value returned for the window is calculated using the exponential-weighted moving average from the beginning of the set ('E'), the last value immediately prior to the current row ('L') or the simple average ('S').

@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.

@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.

@Lag

permits the exponentially weighted moving average to by returned with 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.

Return Type

float

Remarks

If @Id is NULL then @Id = 0.

To calculate daily weighted moving averages, use the DWMA function.

To calculate a moving exponentially weighted moving average, use the MovingEWMA function

Alpha is automatically calculated as 2 / (@Days + 1).

@Lag must be 0 or 1.

@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 exponentially-weighted 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 exponentially weighted moving average

SELECT cast(date_trade as date) as date_trade,

       price_adj_close,

       wct.DEMA(price_adj_close, 5, 0, NULL, 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.

{"columns":[{"field":"date_trade","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"price_adj_close","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"5-day"}],"rows":[{"date_trade":"2012-07-31","price_adj_close":"29.28","5-day":"NULL"},{"date_trade":"2012-08-01","price_adj_close":"29.22","5-day":"NULL"},{"date_trade":"2012-08-02","price_adj_close":"29.00","5-day":"NULL"},{"date_trade":"2012-08-03","price_adj_close":"29.55","5-day":"NULL"},{"date_trade":"2012-08-06","price_adj_close":"29.75","5-day":"29.4492592592593"},{"date_trade":"2012-08-07","price_adj_close":"30.06","5-day":"29.36"},{"date_trade":"2012-08-08","price_adj_close":"30.13","5-day":"29.6166666666667"},{"date_trade":"2012-08-09","price_adj_close":"30.30","5-day":"29.8444444444444"},{"date_trade":"2012-08-10","price_adj_close":"30.22","5-day":"29.9696296296296"},{"date_trade":"2012-08-13","price_adj_close":"30.19","5-day":"30.0430864197531"},{"date_trade":"2012-08-14","price_adj_close":"30.13","5-day":"30.0720576131687"},{"date_trade":"2012-08-15","price_adj_close":"30.20","5-day":"30.1147050754458"},{"date_trade":"2012-08-16","price_adj_close":"30.78","5-day":"30.3364700502972"},{"date_trade":"2012-08-17","price_adj_close":"30.90","5-day":"30.5243133668648"},{"date_trade":"2012-08-20","price_adj_close":"30.74","5-day":"30.5962089112432"},{"date_trade":"2012-08-21","price_adj_close":"30.80","5-day":"30.6641392741621"},{"date_trade":"2012-08-22","price_adj_close":"30.54","5-day":"30.6227595161081"},{"date_trade":"2012-08-23","price_adj_close":"30.26","5-day":"30.5018396774054"},{"date_trade":"2012-08-24","price_adj_close":"30.56","5-day":"30.5212264516036"},{"date_trade":"2012-08-27","price_adj_close":"30.69","5-day":"30.5774843010691"},{"date_trade":"2012-08-28","price_adj_close":"30.63","5-day":"30.5949895340461"},{"date_trade":"2012-08-29","price_adj_close":"30.65","5-day":"30.6133263560307"},{"date_trade":"2012-08-30","price_adj_close":"30.32","5-day":"30.5155509040205"},{"date_trade":"2012-08-31","price_adj_close":"30.82","5-day":"30.6170339360136"},{"date_trade":"2012-09-04","price_adj_close":"30.39","5-day":"30.5413559573424"},{"date_trade":"2012-09-05","price_adj_close":"30.39","5-day":"30.4909039715616"},{"date_trade":"2012-09-06","price_adj_close":"31.35","5-day":"30.7772693143744"},{"date_trade":"2012-09-07","price_adj_close":"30.95","5-day":"30.8348462095829"},{"date_trade":"2012-09-10","price_adj_close":"30.72","5-day":"30.796564139722"},{"date_trade":"2012-09-11","price_adj_close":"30.79","5-day":"30.794376093148"},{"date_trade":"2012-09-12","price_adj_close":"30.78","5-day":"30.7895840620987"},{"date_trade":"2012-09-13","price_adj_close":"30.94","5-day":"30.8397227080658"},{"date_trade":"2012-09-14","price_adj_close":"31.21","5-day":"30.9631484720438"},{"date_trade":"2012-09-17","price_adj_close":"31.21","5-day":"31.0454323146959"},{"date_trade":"2012-09-18","price_adj_close":"31.18","5-day":"31.0902882097973"},{"date_trade":"2012-09-19","price_adj_close":"31.05","5-day":"31.0768588065315"},{"date_trade":"2012-09-20","price_adj_close":"31.45","5-day":"31.2012392043543"},{"date_trade":"2012-09-21","price_adj_close":"31.19","5-day":"31.1974928029029"},{"date_trade":"2012-09-24","price_adj_close":"30.78","5-day":"31.0583285352686"},{"date_trade":"2012-09-25","price_adj_close":"30.39","5-day":"30.8355523568457"},{"date_trade":"2012-09-26","price_adj_close":"30.17","5-day":"30.6137015712305"},{"date_trade":"2012-09-27","price_adj_close":"30.16","5-day":"30.4624677141537"},{"date_trade":"2012-09-28","price_adj_close":"29.76","5-day":"30.2283118094358"},{"date_trade":"2012-10-01","price_adj_close":"29.49","5-day":"29.9822078729572"},{"date_trade":"2012-10-02","price_adj_close":"29.66","5-day":"29.8748052486381"},{"date_trade":"2012-10-03","price_adj_close":"29.86","5-day":"29.8698701657588"},{"date_trade":"2012-10-04","price_adj_close":"30.03","5-day":"29.9232467771725"},{"date_trade":"2012-10-05","price_adj_close":"29.85","5-day":"29.8988311847817"},{"date_trade":"2012-10-08","price_adj_close":"29.78","5-day":"29.8592207898544"},{"date_trade":"2012-10-09","price_adj_close":"29.28","5-day":"29.6661471932363"},{"date_trade":"2012-10-10","price_adj_close":"28.98","5-day":"29.4374314621575"},{"date_trade":"2012-10-11","price_adj_close":"28.95","5-day":"29.274954308105"},{"date_trade":"2012-10-12","price_adj_close":"29.20","5-day":"29.2499695387367"},{"date_trade":"2012-10-15","price_adj_close":"29.51","5-day":"29.3366463591578"},{"date_trade":"2012-10-16","price_adj_close":"29.49","5-day":"29.3877642394385"},{"date_trade":"2012-10-17","price_adj_close":"29.59","5-day":"29.4551761596257"},{"date_trade":"2012-10-18","price_adj_close":"29.50","5-day":"29.4701174397505"},{"date_trade":"2012-10-19","price_adj_close":"28.64","5-day":"29.1934116265003"},{"date_trade":"2012-10-22","price_adj_close":"28.00","5-day":"28.7956077510002"},{"date_trade":"2012-10-23","price_adj_close":"28.05","5-day":"28.5470718340001"},{"date_trade":"2012-10-24","price_adj_close":"27.90","5-day":"28.3313812226668"},{"date_trade":"2012-10-25","price_adj_close":"27.88","5-day":"28.1809208151112"},{"date_trade":"2012-10-26","price_adj_close":"28.21","5-day":"28.1906138767408"},{"date_trade":"2012-10-31","price_adj_close":"28.54","5-day":"28.3070759178272"}]}