How do I achieve a Simple Moving Average crossover? I've looked at some other example/questions ask on here, however they all seem to use an ID column, my table does not have an ID and uses the TIME as a unique field.
SMA Cross over is used to determine a trend in the stock and/or foreign exchange market. For the sake of brevity, the proposed query output below is a 10 and 20 day moving average. However in the real world I will be using a 60 and 270 day average. Following on from the ATR (big thanks to Lucero), there are two answers for calculating an average. The first ATR average was much faster than the second.
I have the following data:
TIME BID-OPEN BID-HIGH BID-LOW BID-CLOSE
28/01/1981 22:00 130.34 130.34 130.34 130.34
29/01/1981 22:00 130.24 130.24 130.24 130.24
02/02/1981 22:00 126.91 126.91 126.91 126.91
03/02/1981 22:00 128.46 128.46 128.46 128.46
04/02/1981 22:00 128.59 128.59 128.59 128.59
05/02/1981 22:00 129.63 129.63 129.63 129.63
09/02/1981 22:00 129.27 129.27 129.27 129.27
10/02/1981 22:00 129.24 129.24 129.24 129.24
11/02/1981 22:00 128.24 128.24 128.24 128.24
12/02/1981 22:00 127.48 127.48 127.48 127.48
17/02/1981 22:00 127.81 127.81 127.81 127.81
18/02/1981 22:00 128.48 128.48 128.48 128.48
19/02/1981 22:00 126.61 126.61 126.61 126.61
23/02/1981 22:00 127.35 127.35 127.35 127.35
24/02/1981 22:00 127.39 127.39 127.39 127.39
25/02/1981 22:00 128.52 128.52 128.52 128.52
26/02/1981 22:00 130.1 130.1 130.1 130.1
02/03/1981 22:00 132.01 132.01 132.01 132.01
03/03/1981 22:00 130.56 130.56 130.56 130.56
04/03/1981 22:00 130.86 130.86 130.86 130.86
05/03/1981 22:00 129.93 129.93 129.93 129.93
09/03/1981 22:00 131.12 131.12 131.12 131.12
10/03/1981 22:00 130.46 130.46 130.46 130.46
11/03/1981 22:00 129.95 129.95 129.95 129.95
12/03/1981 22:00 133.19 133.19 133.19 133.19
16/03/1981 22:00 134.68 134.68 134.68 134.68
17/03/1981 22:00 133.92 133.92 133.92 133.92
18/03/1981 22:00 134.22 134.22 134.22 134.22
19/03/1981 22:00 133.46 133.46 133.46 133.46
23/03/1981 22:00 135.69 135.69 135.69 135.69
24/03/1981 22:00 134.67 134.67 134.67 134.67
The proposed query output below, an average of the [BID-CLOSE] values over the last 10 and 20 days, which will give an output of this:
(ROUND to 2 decimal places)
TIME BID-CLOSE FAST-SMA SLOW-SMA
28/01/1981 22:00 130.34 NULL NULL
29/01/1981 22:00 130.24 NULL NULL
02/02/1981 22:00 126.91 NULL NULL
03/02/1981 22:00 128.46 NULL NULL
04/02/1981 22:00 128.59 NULL NULL
05/02/1981 22:00 129.63 NULL NULL
09/02/1981 22:00 129.27 NULL NULL
10/02/1981 22:00 129.24 NULL NULL
11/02/1981 22:00 128.24 NULL NULL
12/02/1981 22:00 127.48 128.84 NULL
17/02/1981 22:00 127.81 128.59 NULL
18/02/1981 22:00 128.48 128.41 NULL
19/02/1981 22:00 126.61 128.39 NULL
23/02/1981 22:00 127.35 128.27 NULL
24/02/1981 22:00 127.39 128.15 NULL
25/02/1981 22:00 128.52 128.04 NULL
26/02/1981 22:00 130.1 128.12 NULL
02/03/1981 22:00 132.01 128.40 NULL
03/03/1981 22:00 130.56 128.63 NULL
04/03/1981 22:00 130.86 128.97 128.90
05/03/1981 22:00 129.93 129.18 128.89
09/03/1981 22:00 131.12 129.45 128.93
10/03/1981 22:00 130.46 129.83 129.11
11/03/1981 22:00 129.95 130.09 129.18
12/03/1981 22:00 133.19 130.67 129.41
16/03/1981 22:00 134.68 131.29 129.66
17/03/1981 22:00 133.92 131.67 129.90
18/03/1981 22:00 134.22 131.89 130.14
19/03/1981 22:00 133.46 132.18 130.41
23/03/1981 22:00 135.69 132.67 130.82
24/03/1981 22:00 134.67 133.14 131.16
SMA = Simple Moving Average
As a starting point, you would want to use an avg() over(), something like this:
select time,
[bid-close],
avg([bid-close]) over (order by time rows 9 preceding) as [fast-sma]
avg([bid-close]) over (order by time rows 19 preceding) as [slow-sma]
from table