Moving Average Query

[JayKusch]

Have a query that calculates a 7 day moving average. Works great from the 7th day on BUT it is calculating the average on the first six days of the set returned. It should return zero or null for for these days. Here is a snipette of code:


SELECT i1.casino, i1.start_date, i1.sales,
( SELECT AVG( i2.sales )
FROM #dea1 AS i2
WHERE i2.casino = i1.casino
And DATEDIFF( dd , i2.start_date , i1.start_date ) BETWEEN 0 and 6 ) AS MovingAvg

FROM #dea1 AS i1
WHERE start_date between '6/1/2003' AND '6/30/2003' AND casino = 'Test'
GROUP BY i1.casino, i1.start_date, i1.sales
ORDER BY i1.casino , i1.start_date ASC


Thanks

J. Kusch

 

[ClaireHCM]

Could you explain more about "7 day moving average",what does this really mean?
What recourd you dont want,the rows about '6/1/2003' to 6/6/2003'?
Can you give some sample data about the #dea1 table?