Moving Average

[carl]

I have a data set that has daily trading volume for a list of stocks.

I would like to find a sql that will produce a 20 day moving average of all
stocks NOT equal to IBM,AAPL,YHOO, etc...

Is this possible ?

Thank you in advance for your help.

 

[Michel Walsh]

A possible solution:


SELECT a.stockCode, a.theDate, AVG(b.value)

FROM yourTable AS a INNER JOIN yourTable AS b
ON a.stockCode=b.stockCode
AND b.theDate <= a.theDate
AND b.theDate >= a.theDate - 20

WHERE a.stockCode NOT IN( "IBM", "AAPL", "YHOO")

GROUP BY a.stockCode, a.theDate



where I assume your table has the three fields: stockCode, theDate which is
the date for which your table report the value, the third field. So, the
value is dependant of the stockCode and of the supplied theDate. I also
assume theDate has only a date value, not a date and time (ie, the time is
equal to 0), and that you have only one record per stockCode, per date.



Hoping it may help,
Vanderghast, Access MVP