10 Month average
I'm replacing an Excel spreadsheet wit开发者_StackOverflowh a Microsoft SQL Server 2008 database and need to calculate this:
=AVERAGE(IF((A:A>A4-304)*(A:A<=A4),G:G))
Where column A is the date and column G is the value. The heading for this field is:
10 month simple moving average
I suppose I will use something like:
SELECT RawData.*
,(SELECT Sum(X.AdjClose) AS SumAdjClose
FROM RawData X
WHERE DATEDIFF(d,X.RawDate,RawData.RawDate) <= 304
) AS SumAdjClose
FROM RawData
In SQL Server one option is to make the filters in the WHERE
clause and the average in the SELECT
select avg(ValueCol)
from Table1
where DateCol between @Date-304 and @Date
Another option, in the same logic of the excel could be:
select avg(case when DateCol between @Date-304 and @Date then ValueCol else null)
from Table1
精彩评论