Create a moving average using a 343 minute scale in SQL Server Query

Hello

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)
AS
(
SELECT [ID],
       [Last],
       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
FROM   [Blockchain].[dbo].[vw_BassLineData]
)
SELECT MarketDate,
       RowNumber,
       ClosingPrice,
       IIF(RowNumber > 6, MA7, NULL) MA7,
       IIF(RowNumber > 76, MA77, NULL) MA77,
       CASE
          WHEN RowNumber > 76 AND MA7 > MA77 THEN 'Over'
          WHEN RowNumber > 76 AND MA7 < MA77 THEN 'Below'
          ELSE NULL
       END as Signal
FROM   CTE_DailyQuote
ORDER BY MarketDate desc

Open in new window


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 BTC-LTC-MovingAverage-343---7---77.PNG
Thanks in advance :-)
MovingAverageData1Minute.xlsx
deanmachine333Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
What changes you need in the query you have given? There in only 1 tab in the excel you have attached.
If possible can you give me input sample data and the expected output in the excel file.

Thanks
0
Pawan KumarDatabase ExpertCommented:
Edited my last comment.
0
deanmachine333Author Commented:
I expect im trying to group the minutes into 343 minutes as the timescale and then use the 7min and 77 min on top of that , so in the chart you can see that the time scale is in 343 and the Green line is the 7 min Average , and the orange is the 77 min average plotted over a 343 min timescale.


does that make sense?

thanks for looking
MovingAverageData1Minute.xlsx
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Pawan KumarDatabase ExpertCommented:
Hi,
Your query looks fine to me. Do you need any more/specific changes into it.
0
deanmachine333Author Commented:
that query is plotting the moving averages of 7 and 77 on  a 1 minute time scale i need it on a 343 minute time scale :-)
0
Pawan KumarDatabase ExpertCommented:
can you tell me logically what needs to be done. I am not much into charts.
0
deanmachine333Author Commented:
no probs so at the bottom of the chart it is split into 343 minutes sections, so each little candle either red or green is a 343 minute section and the then you have the green line which is the 7 min moving average , and orange line is the 77 minute average line.

My script is at present is only plotting on a 1-minute scale so doesn't match the chart i want to amend my script to us the 343min time scale

thanks
0
Jayadev NairApplication DeveloperCommented:
So your "table is inserting a record on every 1 minute now and you are changing that to one record every 343 minutes"

Is that a right statement?
0
deanmachine333Author Commented:
Hello Jayadev,

I have records inserted every 1 minute , that is correct and at present i have got my script to plot using that to plot every minute and on the minute i have calculated the last 7minutes ( 7 rows ) the average of those which gives me my Moving Average 7 , and did the same for the Moving Average 77 minutes. What i would like to have now is it to plot every 343 Minutes instead but still using the 7 Minute Moving Average and 77 Minute Moving Average.

In the chart the "Candles" the green and red bars is the value at that interval of either 1 minute ( which im currently doing ) , i need each candle to only appear every 343 minutes interval .

So i have attached a 1 minute interval chart and a 343 minute interval chart , at the bottom of the chart you can see the time, Green line = 7 Minute Average and Orange line = 77 Minute Average.
BTC-LTC-1-minute-chart-data.PNG
BTC-LTC-343-minute-chart-data.PNG
0
deanmachine333Author Commented:
hello , i might be thinking that the Moving Average of 7 or 77 could just be the 7 intervals average so if on a 1 minute interval plot then the 7 MA is 7 minutes and 77 minutes , but maybe on a 343 Minute interval the , 7 MA is on the last 7 343 intervals and same for 77 MA . does that make sense?

So would i need to change the base table into a 343 Minute grouping and then take that Last value at each interval and use by getting the last 7 of those values to give me the 7 MA over the 343 minute interval and same applies to the 77MA
0
Pawan KumarDatabase ExpertCommented:
Please try this-

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY HourMin ASC) rnk FROM 
	(
		SELECT [timestamp], id ,  [Date] , HourMin,[Min],[last]
		,CASE WHEN ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC)  % 343 = 0 THEN 343 ELSE
				ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC) % 343 END Gr
		FROM 
		[dbo].[LTC1minData]
	)r WHERE gr = 343
)
,CTE_DailyQuote (MarketDate, ClosingPrice, RowNumber, MA7, MA77)
AS
(
	SELECT [ID],
		   [Last],
		   ROW_NUMBER() OVER (ORDER BY rnk ASC) RowNumber,
		   AVG(CAST([Last] AS DECIMAL(30,8))) OVER (ORDER BY rnk ASC ROWS 6 PRECEDING) AS MA7,
		   AVG(CAST([Last] AS DECIMAL(30,8))) OVER (ORDER BY rnk ASC ROWS 76 PRECEDING) AS MA77
	FROM   CTE
)
SELECT MarketDate,
       RowNumber,
       ClosingPrice,
       IIF(RowNumber > 6, MA7, NULL) MA7,
       IIF(RowNumber > 76, MA77, NULL) MA77,
       CASE
          WHEN RowNumber > 76 AND MA7 > MA77 THEN 'Over'
          WHEN RowNumber > 76 AND MA7 < MA77 THEN 'Below'
          ELSE NULL
       END as Signal
FROM   CTE_DailyQuote
ORDER BY MarketDate desc

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Please try the tested solution for you -
Basically we have to find the range of 343 minutes and then take last 7 average from there and 77 average from there. Please try and let me know in case you need any changes.

;WITH CTE AS
(
	SELECT * FROM 
	(
		SELECT [timestamp], id ,  [Date] , HourMin,[Min],[last]
		,CASE WHEN ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC)  % 343 = 0 THEN 343 ELSE
				ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC) % 343 END Gr
		,ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC) rnk 
		FROM 
		[dbo].[LTC1minData]
	)r WHERE gr = 343
)
,CTE12 AS
(
	SELECT * FROM 
	(
		SELECT [timestamp], id ,  [Date] , HourMin,[Min],[last]
		,CASE WHEN ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC)  % 343 = 0 THEN 343 ELSE
				ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC) % 343 END Gr
		,ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY HourMin ASC) rnk 
		FROM 
		[dbo].[LTC1minData]
	)r
)
SELECT [timestamp], id ,  [Date] , HourMin,[Min],[last],Gr,MA7,MA77 FROM CTE c
CROSS APPLY
(
	SELECT TOP 1 AVG(CAST([Last] AS DECIMAL(30,8))) MA7		   
	FROM CTE12 L
	WHERE L.Date = c.Date AND rnk BETWEEN c.Gr - 7 AND c.Gr
)r
CROSS APPLY
(
	SELECT AVG(CAST([Last] AS DECIMAL(30,8))) MA77
	FROM CTE12 L
	WHERE L.Date = c.Date AND rnk BETWEEN c.Gr - 77 AND c.Gr
)r1

Open in new window



OUTPUT

/*------------------------
OUTPUT
------------------------*/
timestamp                   id                        Date         HourMin   Min    last       Gr    MA7          MA77
--------------------------- -------------------------------------- --------- ------ ---------------- ------------ ----------
2017-11-18T15:46:45.563     BTC-LTC_2017-11-18_15:46  2017-11-18   15:46     46     0.00877527 343   0.00877928   0.00875511
2017-11-19T19:13:51.517     BTC-LTC_2017-11-19_19:13  2017-11-19   19:13     13     0.00883796 343   0.00889917   0.00880641
2017-11-20T05:41:51.35      BTC-LTC_2017-11-20_05:41  2017-11-20   5:41      41     0.008962   343   0.00896959   0.00896340

(3 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
question abandoned
provided solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.