I have a table that holds a ID that is made up of a Value+Date+hourminute , RowNumber , ClosingPrice, MovingAverage7 (7minutes), MovingAverage77 (77 minutes) and Signal ( when 7 is iether over or below 77 )
My table is populated every minute and im looking to try and change my query to be able to change the timescale from 1 minute to 343 minutes. So should basically get 343 minutes average and on that get the 7 minute and 77 minute average.
I have attached my data output from this query
WITH CTE_DailyQuote (MarketDate, ClosingPrice, RowNumber, MA7, MA77)
ROW_NUMBER() OVER (ORDER BY [ID] ASC) RowNumber,
AVG([Last]) OVER (ORDER BY [ID] ASC ROWS 6 PRECEDING) AS MA7,
AVG([Last]) OVER (ORDER BY [ID] ASC ROWS 76 PRECEDING) AS MA77
IIF(RowNumber > 6, MA7, NULL) MA7,
IIF(RowNumber > 76, MA77, NULL) MA77,
WHEN RowNumber > 76 AND MA7 > MA77 THEN 'Over'
WHEN RowNumber > 76 AND MA7 < MA77 THEN 'Below'
END as Signal
ORDER BY MarketDate desc
I have attached my moving average results and also what the table looks like that my above query runs on in 2 tabs.
I have also attached what the Chart should look like
Thanks in advance :-)