— TEST DATA
DECLARE @DailyQuote TABLE
(
MarketDate DATE,
ClosingPrice float
)
INSERT INTO @DailyQuote VALUES (‘2016/12/30’, 62.14)
INSERT INTO @DailyQuote VALUES (‘2016/12/29’, 62.90)
INSERT INTO @DailyQuote VALUES (‘2016/12/28’, 62.99)
INSERT INTO @DailyQuote VALUES (‘2016/12/27’, 63.28)
INSERT INTO @DailyQuote VALUES (‘2016/12/23’, 63.24)
INSERT INTO @DailyQuote VALUES (‘2016/12/22’, 63.55)
INSERT INTO @DailyQuote VALUES (‘2016/12/21’, 63.54)
INSERT INTO @DailyQuote VALUES (‘2016/12/20’, 63.54)
INSERT INTO @DailyQuote VALUES (‘2016/12/19’, 63.62)
INSERT INTO @DailyQuote VALUES (‘2016/12/16’, 62.30)
INSERT INTO @DailyQuote VALUES (‘2016/12/15’, 62.58)
INSERT INTO @DailyQuote VALUES (‘2016/12/14’, 62.68)
INSERT INTO @DailyQuote VALUES (‘2016/12/13’, 62.98)
INSERT INTO @DailyQuote VALUES (‘2016/12/12’, 62.17)
INSERT INTO @DailyQuote VALUES (‘2016/12/09’, 61.97)
INSERT INTO @DailyQuote VALUES (‘2016/12/08’, 61.01)
INSERT INTO @DailyQuote VALUES (‘2016/12/07’, 61.37)
INSERT INTO @DailyQuote VALUES (‘2016/12/06’, 59.95)
INSERT INTO @DailyQuote VALUES (‘2016/12/05’, 60.22)
INSERT INTO @DailyQuote VALUES (‘2016/12/02’, 59.25)
INSERT INTO @DailyQuote VALUES (‘2016/12/01’, 59.20)
INSERT INTO @DailyQuote VALUES (‘2016/11/30’, 60.26)
INSERT INTO @DailyQuote VALUES (‘2016/11/29’, 61.09)
INSERT INTO @DailyQuote VALUES (‘2016/11/28’, 60.61)
INSERT INTO @DailyQuote VALUES (‘2016/11/25’, 60.53)
INSERT INTO @DailyQuote VALUES (‘2016/11/23’, 60.40)
INSERT INTO @DailyQuote VALUES (‘2016/11/22’, 61.12)
INSERT INTO @DailyQuote VALUES (‘2016/11/21’, 60.86)
INSERT INTO @DailyQuote VALUES (‘2016/11/18’, 60.35)
INSERT INTO @DailyQuote VALUES (‘2016/11/17’, 60.64)
INSERT INTO @DailyQuote VALUES (‘2016/11/16’, 59.65)
INSERT INTO @DailyQuote VALUES (‘2016/11/15’, 58.87)
INSERT INTO @DailyQuote VALUES (‘2016/11/14’, 58.12)
INSERT INTO @DailyQuote VALUES (‘2016/11/11’, 59.02)
INSERT INTO @DailyQuote VALUES (‘2016/11/10’, 58.70)
INSERT INTO @DailyQuote VALUES (‘2016/11/09’, 60.17)
INSERT INTO @DailyQuote VALUES (‘2016/11/08’, 60.47)
INSERT INTO @DailyQuote VALUES (‘2016/11/07’, 60.42)
INSERT INTO @DailyQuote VALUES (‘2016/11/04’, 58.71)
INSERT INTO @DailyQuote VALUES (‘2016/11/03’, 59.21)
INSERT INTO @DailyQuote VALUES (‘2016/11/02’, 59.43)
INSERT INTO @DailyQuote VALUES (‘2016/11/01’, 59.80)
INSERT INTO @DailyQuote VALUES (‘2016/10/31’, 59.92)
INSERT INTO @DailyQuote VALUES (‘2016/10/28’, 59.87)
INSERT INTO @DailyQuote VALUES (‘2016/10/27’, 60.10)
INSERT INTO @DailyQuote VALUES (‘2016/10/26’, 60.63)
INSERT INTO @DailyQuote VALUES (‘2016/10/25’, 60.99)
INSERT INTO @DailyQuote VALUES (‘2016/10/24’, 61.00)
INSERT INTO @DailyQuote VALUES (‘2016/10/21’, 59.66)
INSERT INTO @DailyQuote VALUES (‘2016/10/20’, 57.25)
INSERT INTO @DailyQuote VALUES (‘2016/10/19’, 57.53)
INSERT INTO @DailyQuote VALUES (‘2016/10/18’, 57.66)
INSERT INTO @DailyQuote VALUES (‘2016/10/17’, 57.22)
INSERT INTO @DailyQuote VALUES (‘2016/10/14’, 57.42)
INSERT INTO @DailyQuote VALUES (‘2016/10/13’, 56.92)
INSERT INTO @DailyQuote VALUES (‘2016/10/12’, 57.11)
INSERT INTO @DailyQuote VALUES (‘2016/10/11’, 57.19)
INSERT INTO @DailyQuote VALUES (‘2016/10/10’, 58.04)
INSERT INTO @DailyQuote VALUES (‘2016/10/07’, 57.80)
INSERT INTO @DailyQuote VALUES (‘2016/10/06’, 57.74)
INSERT INTO @DailyQuote VALUES (‘2016/10/05’, 57.64)
INSERT INTO @DailyQuote VALUES (‘2016/10/04’, 57.24)
INSERT INTO @DailyQuote VALUES (‘2016/10/03’, 57.42)
INSERT INTO @DailyQuote VALUES (‘2016/09/30’, 57.60)
INSERT INTO @DailyQuote VALUES (‘2016/09/29’, 57.40)
INSERT INTO @DailyQuote VALUES (‘2016/09/28’, 58.03)
INSERT INTO @DailyQuote VALUES (‘2016/09/27’, 57.95)
INSERT INTO @DailyQuote VALUES (‘2016/09/26’, 56.90)
INSERT INTO @DailyQuote VALUES (‘2016/09/23’, 57.43)
INSERT INTO @DailyQuote VALUES (‘2016/09/22’, 57.82)
INSERT INTO @DailyQuote VALUES (‘2016/09/21’, 57.76)
INSERT INTO @DailyQuote VALUES (‘2016/09/20’, 56.81)
INSERT INTO @DailyQuote VALUES (‘2016/09/19’, 56.93)
INSERT INTO @DailyQuote VALUES (‘2016/09/16’, 57.25)
INSERT INTO @DailyQuote VALUES (‘2016/09/15’, 57.19)
INSERT INTO @DailyQuote VALUES (‘2016/09/14’, 56.26)
INSERT INTO @DailyQuote VALUES (‘2016/09/13’, 56.53)
INSERT INTO @DailyQuote VALUES (‘2016/09/12’, 57.05)
INSERT INTO @DailyQuote VALUES (‘2016/09/09’, 56.21)
INSERT INTO @DailyQuote VALUES (‘2016/09/08’, 57.43)
INSERT INTO @DailyQuote VALUES (‘2016/09/07’, 57.66)
INSERT INTO @DailyQuote VALUES (‘2016/09/06’, 57.61)
INSERT INTO @DailyQuote VALUES (‘2016/09/02’, 57.67)
INSERT INTO @DailyQuote VALUES (‘2016/09/01’, 57.59)
INSERT INTO @DailyQuote VALUES (‘2016/08/31’, 57.46)
INSERT INTO @DailyQuote VALUES (‘2016/08/30’, 57.89)
INSERT INTO @DailyQuote VALUES (‘2016/08/29’, 58.10)
INSERT INTO @DailyQuote VALUES (‘2016/08/26’, 58.03)
INSERT INTO @DailyQuote VALUES (‘2016/08/25’, 58.17)
INSERT INTO @DailyQuote VALUES (‘2016/08/24’, 57.95)
INSERT INTO @DailyQuote VALUES (‘2016/08/23’, 57.89)
INSERT INTO @DailyQuote VALUES (‘2016/08/22’, 57.67)
INSERT INTO @DailyQuote VALUES (‘2016/08/19’, 57.62)
INSERT INTO @DailyQuote VALUES (‘2016/08/18’, 57.60)
INSERT INTO @DailyQuote VALUES (‘2016/08/17’, 57.56)
INSERT INTO @DailyQuote VALUES (‘2016/08/16’, 57.44)
INSERT INTO @DailyQuote VALUES (‘2016/08/15’, 58.12)
INSERT INTO @DailyQuote VALUES (‘2016/08/12’, 57.94)
INSERT INTO @DailyQuote VALUES (‘2016/08/11’, 58.30)
INSERT INTO @DailyQuote VALUES (‘2016/08/10’, 58.02)
INSERT INTO @DailyQuote VALUES (‘2016/08/09’, 58.20)
INSERT INTO @DailyQuote VALUES (‘2016/08/08’, 58.06)
INSERT INTO @DailyQuote VALUES (‘2016/08/05’, 57.96)
INSERT INTO @DailyQuote VALUES (‘2016/08/04’, 57.39)
INSERT INTO @DailyQuote VALUES (‘2016/08/03’, 56.97)
INSERT INTO @DailyQuote VALUES (‘2016/08/02’, 56.58)
INSERT INTO @DailyQuote VALUES (‘2016/08/01’, 56.58)
INSERT INTO @DailyQuote VALUES (‘2016/07/29’, 56.68)
INSERT INTO @DailyQuote VALUES (‘2016/07/28’, 56.21)
INSERT INTO @DailyQuote VALUES (‘2016/07/27’, 56.19)
INSERT INTO @DailyQuote VALUES (‘2016/07/26’, 56.76)
INSERT INTO @DailyQuote VALUES (‘2016/07/25’, 56.73)
INSERT INTO @DailyQuote VALUES (‘2016/07/22’, 56.57)
INSERT INTO @DailyQuote VALUES (‘2016/07/21’, 55.80)
INSERT INTO @DailyQuote VALUES (‘2016/07/20’, 55.91)
INSERT INTO @DailyQuote VALUES (‘2016/07/19’, 53.09)
INSERT INTO @DailyQuote VALUES (‘2016/07/18’, 53.96)
INSERT INTO @DailyQuote VALUES (‘2016/07/15’, 53.70)
INSERT INTO @DailyQuote VALUES (‘2016/07/14’, 53.74)
INSERT INTO @DailyQuote VALUES (‘2016/07/13’, 53.51)
INSERT INTO @DailyQuote VALUES (‘2016/07/12’, 53.21)
INSERT INTO @DailyQuote VALUES (‘2016/07/11’, 52.59)
INSERT INTO @DailyQuote VALUES (‘2016/07/08’, 52.30)
INSERT INTO @DailyQuote VALUES (‘2016/07/07’, 51.38)
INSERT INTO @DailyQuote VALUES (‘2016/07/06’, 51.38)
INSERT INTO @DailyQuote VALUES (‘2016/07/05’, 51.17)
INSERT INTO @DailyQuote VALUES (‘2016/07/01’, 51.16)
INSERT INTO @DailyQuote VALUES (‘2016/06/30’, 51.17)
INSERT INTO @DailyQuote VALUES (‘2016/06/29’, 50.54)
INSERT INTO @DailyQuote VALUES (‘2016/06/28’, 49.44)
INSERT INTO @DailyQuote VALUES (‘2016/06/27’, 48.43)
INSERT INTO @DailyQuote VALUES (‘2016/06/24’, 49.83)
INSERT INTO @DailyQuote VALUES (‘2016/06/23’, 51.91)
INSERT INTO @DailyQuote VALUES (‘2016/06/22’, 50.99)
INSERT INTO @DailyQuote VALUES (‘2016/06/21’, 51.19)
INSERT INTO @DailyQuote VALUES (‘2016/06/20’, 50.07)
INSERT INTO @DailyQuote VALUES (‘2016/06/17’, 50.13)
INSERT INTO @DailyQuote VALUES (‘2016/06/16’, 50.39)
INSERT INTO @DailyQuote VALUES (‘2016/06/15’, 49.69)
INSERT INTO @DailyQuote VALUES (‘2016/06/14’, 49.83)
INSERT INTO @DailyQuote VALUES (‘2016/06/13’, 50.14)
INSERT INTO @DailyQuote VALUES (‘2016/06/10’, 51.48)
INSERT INTO @DailyQuote VALUES (‘2016/06/09’, 51.62)
INSERT INTO @DailyQuote VALUES (‘2016/06/08’, 52.04)
INSERT INTO @DailyQuote VALUES (‘2016/06/07’, 52.10)
INSERT INTO @DailyQuote VALUES (‘2016/06/06’, 52.13)
INSERT INTO @DailyQuote VALUES (‘2016/06/03’, 51.79)
INSERT INTO @DailyQuote VALUES (‘2016/06/02’, 52.48)
INSERT INTO @DailyQuote VALUES (‘2016/06/01’, 52.85)
INSERT INTO @DailyQuote VALUES (‘2016/05/31’, 53.00)
INSERT INTO @DailyQuote VALUES (‘2016/05/27’, 52.32)
INSERT INTO @DailyQuote VALUES (‘2016/05/26’, 51.89)
INSERT INTO @DailyQuote VALUES (‘2016/05/25’, 52.12)
INSERT INTO @DailyQuote VALUES (‘2016/05/24’, 51.59)
INSERT INTO @DailyQuote VALUES (‘2016/05/23’, 50.03)
INSERT INTO @DailyQuote VALUES (‘2016/05/20’, 50.62)
INSERT INTO @DailyQuote VALUES (‘2016/05/19’, 50.32)
INSERT INTO @DailyQuote VALUES (‘2016/05/18’, 50.81)
INSERT INTO @DailyQuote VALUES (‘2016/05/17’, 50.51)
INSERT INTO @DailyQuote VALUES (‘2016/05/16’, 51.83)
INSERT INTO @DailyQuote VALUES (‘2016/05/13’, 51.08)
INSERT INTO @DailyQuote VALUES (‘2016/05/12’, 51.51)
INSERT INTO @DailyQuote VALUES (‘2016/05/11’, 51.05)
INSERT INTO @DailyQuote VALUES (‘2016/05/10’, 51.02)
INSERT INTO @DailyQuote VALUES (‘2016/05/09’, 50.07)
INSERT INTO @DailyQuote VALUES (‘2016/05/06’, 50.39)
INSERT INTO @DailyQuote VALUES (‘2016/05/05’, 49.94)
INSERT INTO @DailyQuote VALUES (‘2016/05/04’, 49.87)
INSERT INTO @DailyQuote VALUES (‘2016/05/03’, 49.78)
INSERT INTO @DailyQuote VALUES (‘2016/05/02’, 50.61)
INSERT INTO @DailyQuote VALUES (‘2016/04/29’, 49.87)
INSERT INTO @DailyQuote VALUES (‘2016/04/28’, 49.90)
INSERT INTO @DailyQuote VALUES (‘2016/04/27’, 50.94)
INSERT INTO @DailyQuote VALUES (‘2016/04/26’, 51.44)
INSERT INTO @DailyQuote VALUES (‘2016/04/25’, 52.11)
INSERT INTO @DailyQuote VALUES (‘2016/04/22’, 51.78)
INSERT INTO @DailyQuote VALUES (‘2016/04/21’, 55.78)
INSERT INTO @DailyQuote VALUES (‘2016/04/20’, 55.59)
INSERT INTO @DailyQuote VALUES (‘2016/04/19’, 56.39)
INSERT INTO @DailyQuote VALUES (‘2016/04/18’, 56.46)
INSERT INTO @DailyQuote VALUES (‘2016/04/15’, 55.65)
INSERT INTO @DailyQuote VALUES (‘2016/04/14’, 55.36)
INSERT INTO @DailyQuote VALUES (‘2016/04/13’, 55.35)
INSERT INTO @DailyQuote VALUES (‘2016/04/12’, 54.65)
INSERT INTO @DailyQuote VALUES (‘2016/04/11’, 54.31)
INSERT INTO @DailyQuote VALUES (‘2016/04/08’, 54.42)
INSERT INTO @DailyQuote VALUES (‘2016/04/07’, 54.46)
INSERT INTO @DailyQuote VALUES (‘2016/04/06’, 55.12)
INSERT INTO @DailyQuote VALUES (‘2016/04/05’, 54.56)
INSERT INTO @DailyQuote VALUES (‘2016/04/04’, 55.43)
INSERT INTO @DailyQuote VALUES (‘2016/04/01’, 55.57)
INSERT INTO @DailyQuote VALUES (‘2016/03/31’, 55.23)
INSERT INTO @DailyQuote VALUES (‘2016/03/30’, 55.05)
INSERT INTO @DailyQuote VALUES (‘2016/03/29’, 54.71)
INSERT INTO @DailyQuote VALUES (‘2016/03/28’, 53.54)
INSERT INTO @DailyQuote VALUES (‘2016/03/24’, 54.21)
INSERT INTO @DailyQuote VALUES (‘2016/03/23’, 53.97)
INSERT INTO @DailyQuote VALUES (‘2016/03/22’, 54.07)
INSERT INTO @DailyQuote VALUES (‘2016/03/21’, 53.86)
INSERT INTO @DailyQuote VALUES (‘2016/03/18’, 53.49)
INSERT INTO @DailyQuote VALUES (‘2016/03/17’, 54.66)
INSERT INTO @DailyQuote VALUES (‘2016/03/16’, 54.35)
INSERT INTO @DailyQuote VALUES (‘2016/03/15’, 53.59)
INSERT INTO @DailyQuote VALUES (‘2016/03/14’, 53.17)
INSERT INTO @DailyQuote VALUES (‘2016/03/11’, 53.07)
INSERT INTO @DailyQuote VALUES (‘2016/03/10’, 52.05)
INSERT INTO @DailyQuote VALUES (‘2016/03/09’, 52.84)
INSERT INTO @DailyQuote VALUES (‘2016/03/08’, 51.65)
INSERT INTO @DailyQuote VALUES (‘2016/03/07’, 51.03)
INSERT INTO @DailyQuote VALUES (‘2016/03/04’, 52.03)
INSERT INTO @DailyQuote VALUES (‘2016/03/03’, 52.35)
INSERT INTO @DailyQuote VALUES (‘2016/03/02’, 52.95)
INSERT INTO @DailyQuote VALUES (‘2016/03/01’, 52.58)
INSERT INTO @DailyQuote VALUES (‘2016/02/29’, 50.88)
INSERT INTO @DailyQuote VALUES (‘2016/02/26’, 51.30)
INSERT INTO @DailyQuote VALUES (‘2016/02/25’, 52.10)
INSERT INTO @DailyQuote VALUES (‘2016/02/24’, 51.36)
INSERT INTO @DailyQuote VALUES (‘2016/02/23’, 51.18)
INSERT INTO @DailyQuote VALUES (‘2016/02/22’, 52.65)
INSERT INTO @DailyQuote VALUES (‘2016/02/19’, 51.82)
INSERT INTO @DailyQuote VALUES (‘2016/02/18’, 52.19)
INSERT INTO @DailyQuote VALUES (‘2016/02/17’, 52.42)
INSERT INTO @DailyQuote VALUES (‘2016/02/16’, 51.09)
INSERT INTO @DailyQuote VALUES (‘2016/02/12’, 50.50)
INSERT INTO @DailyQuote VALUES (‘2016/02/11’, 49.69)
INSERT INTO @DailyQuote VALUES (‘2016/02/10’, 49.71)
INSERT INTO @DailyQuote VALUES (‘2016/02/09’, 49.28)
INSERT INTO @DailyQuote VALUES (‘2016/02/08’, 49.41)
INSERT INTO @DailyQuote VALUES (‘2016/02/05’, 50.16)
INSERT INTO @DailyQuote VALUES (‘2016/02/04’, 52.00)
INSERT INTO @DailyQuote VALUES (‘2016/02/03’, 52.16)
INSERT INTO @DailyQuote VALUES (‘2016/02/02’, 53.00)
INSERT INTO @DailyQuote VALUES (‘2016/02/01’, 54.71)
INSERT INTO @DailyQuote VALUES (‘2016/01/29’, 55.09)
INSERT INTO @DailyQuote VALUES (‘2016/01/28’, 52.06)
INSERT INTO @DailyQuote VALUES (‘2016/01/27’, 51.22)
INSERT INTO @DailyQuote VALUES (‘2016/01/26’, 52.17)
INSERT INTO @DailyQuote VALUES (‘2016/01/25’, 51.79)
INSERT INTO @DailyQuote VALUES (‘2016/01/22’, 52.29)
INSERT INTO @DailyQuote VALUES (‘2016/01/21’, 50.48)
INSERT INTO @DailyQuote VALUES (‘2016/01/20’, 50.79)
INSERT INTO @DailyQuote VALUES (‘2016/01/19’, 50.56)
INSERT INTO @DailyQuote VALUES (‘2016/01/15’, 50.99)
INSERT INTO @DailyQuote VALUES (‘2016/01/14’, 53.11)
INSERT INTO @DailyQuote VALUES (‘2016/01/13’, 51.64)
INSERT INTO @DailyQuote VALUES (‘2016/01/12’, 52.78)
INSERT INTO @DailyQuote VALUES (‘2016/01/11’, 52.30)
INSERT INTO @DailyQuote VALUES (‘2016/01/08’, 52.33)
INSERT INTO @DailyQuote VALUES (‘2016/01/07’, 52.17)
INSERT INTO @DailyQuote VALUES (‘2016/01/06’, 54.05)
INSERT INTO @DailyQuote VALUES (‘2016/01/05’, 55.05)
INSERT INTO @DailyQuote VALUES (‘2016/01/04’, 54.80);
–Answer
WITH CTE_DailyQuote (MarketDate, ClosingPrice, RowNumber, MA10, MA30)
AS
(
SELECT MarketDate,
ClosingPrice,
ROW_NUMBER() OVER (ORDER BY MarketDate ASC) RowNumber,
AVG(ClosingPrice) OVER (ORDER BY MarketDate ASC ROWS 9 PRECEDING) AS MA10,
AVG(ClosingPrice) OVER (ORDER BY MarketDate ASC ROWS 29 PRECEDING) AS MA30
FROM @DailyQuote
)
SELECT MarketDate,
RowNumber,
ClosingPrice,
IIF(RowNumber > 9, MA10, NULL) MA10,
IIF(RowNumber > 29, MA30, NULL) MA30,
CASE
WHEN RowNumber > 29 AND MA10 > MA30 THEN ‘Over’
WHEN RowNumber > 29 AND MA10 < MA30 THEN ‘Below’
ELSE NULL
END as TradeSignal
FROM CTE_DailyQuote
