Rolling average not based on date range

[hakannordell]

Hi, I'm trying to write an SQL statement where I calculate rolling averages for different stocks, but I'm stuck now and hoping for your help. My tables contain "ShareName", "DayDate" and "EndPrice", and I want a rolling average for "EndPrice".

The code I wrote counts averages for, for example, 5 days, but since I do not have values ​​for all days when the stock exchange is closed during the weekends, the code will count incorrectly when there are holes for weekends in the table. I would instead like it to calculate the mean value based on the number of records, for example the 5 previous records, and then sorted by date.

The other problem I have is that I want it to store the rolling average of each item in the table.


Please /Håkan

DayDate	ShareName	EndPrice
2021-12-01	A	5
2021-12-02	A	4
2021-12-04	A	5
2021-12-01	B	2
2021-12-02	B	3

SELECT A.ShareName, A.DayDate, (SELECT AVG(B.EndPrice) FROM MyTable AS B WHERE
B.ShareName = A.ShareName AND
B.DayDate >= DateAdd("d",-6, A.DayDate) AND B.DayDate <= A.DayDate) AS MA5
FROM MyTable AS A
ORDER BY A.ShareName, A.DayDate;

[hakannordell]

No one has any clue?

[CJ_London]

how do you define 'previous' and what do you expect the end result to look like from your example data? Since there are only 3 and 2 records should these be ignored until there are 5 records or do you want and average of 1, 2 e etc as the count increases

Best I can suggest at the moment is to look at using the 'top 5' predicate to get your top 5 by share name, then average that

Don't have time right now to make up some data

[ArviLaanemets]

Add a prefilled calendary table, with all dates listed as primary key. Then you can query last 5 days (between todays date -5 and todays date) from this calendary table and left join them with you prices table, with condition "DayDate is Not Null" , and use this query as source to calculate the average price.