Rolling average in a query? 1

[Senjen]

what I want to do is compare the average monthly sales over the last 12 months with this months sales. The original table has the following fields: ID, StartDate, QBSales among others. The query where I am comparing data sorta looks like this: StartDate QBSales ThisMonth
1/1/1994 9,005.00 23928
2/1/1994 8,521.00 23929
3/1/1994 9,743.00 23930

I would like to add a column where it shows the average sales for the 12 months leading up to the month in the record. So in my example there would be an additional column as follows:
StartDate QBSales ThisMonth 12MoRollAvg
1/1/1994 9,005.00 23928 (avg of QBSales for months 23917-23928)
2/1/1994 8,521.00 23929 (avg of QBSales for months 23918-23929)
3/1/1994 9,743.00 23930 (avg of QBSales for months 23919-23930)

any help would be appreciated.

 

[r937]

select StartDate  
     , QBSales  
     , ThisMonth  
     , ( select sum(QBSales) 
              / count(ThisMonth)
           from daTable
          where ThisMonth
             between foo.ThisMonth - 12
                 and ThisMonth ) 
          as 12MoRollAvg
  from daTable as foo

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Senjen]

Using the given code this was my result (data pulled from aqryAllSales since there is no field named ThisMonth in the table):
StartDate QBSales ThisMonth 12MoRollAvg
1/1/1994 $9,005.00 23928 $63,060.60
2/1/1994 $8,521.00 23929 $63,060.60
3/1/1994 $9,743.00 23930 $63,060.60
4/1/1994 $11,067.00 23931 $63,060.60
5/1/1994 $8,867.00 23932 $63,060.60
6/1/1994 $9,385.00 23933 $63,060.60
7/1/1994 $14,721.00 23934 $63,060.60
8/1/1994 $10,591.00 23935 $63,060.60
9/1/1994 $9,761.00 23936 $63,060.60
10/1/1994 $9,359.00 23937 $63,060.60
11/1/1994 $7,980.00 23938 $63,060.60
12/1/1994 $7,893.00 23939 $63,060.60
1/1/1995 $9,650.00 23940 $63,060.60
2/1/1995 $9,731.00 23941 $63,060.60
3/1/1995 $15,328.00 23942 $63,060.60
4/1/1995 $14,019.00 23943 $63,060.60
5/1/1995 $12,935.00 23944 $63,060.60
6/1/1995 $10,319.00 23945 $63,060.60
7/1/1995 $10,811.00 23946 $63,060.60
8/1/1995 $10,245.00 23947 $63,060.60
9/1/1995 $13,192.00 23948 $63,060.60
10/1/1995 $8,177.00 23949 $63,060.60
11/1/1995 $9,347.00 23950 $63,060.60
12/1/1995 $8,911.00 23951 $63,060.60

 

[PHV]

Something like this ?
SELECT A.StartDate, A.QBSales, A.ThisMonth, Avg(B.QBSales) AS 12MoRollAvg
FROM aqryAllSales AS A INNER JOIN aqryAllSales AS B ON A.ThisMonth >= B.ThisMonth AND A.ThisMonth <= B.ThisMonth+11
GROUP BY A.StartDate, A.QBSales, A.ThisMonth;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Senjen]

Top notch, works perfectly. Thank you both very much. I have been out of coding for some time and your posts were both very helpful and educational. Thanks again.