How do I calculate a 3 year moving average from within a stored procedure?  I
can do an average for each year, but not a moving average.  Thanks

An average of what?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.




Show quoteHide quote

The query selects the year, sum of outage minutes, number of customers, and
number of customers per feeder.  Sample data:

Year      Minutes      Customers  Customers/Feeder
1998    220    62900    1000
1999    1258    63735    1000
2000    320    64868    1000
2001    875    65107    1000
2002    621    66458    1000
2003    63    66458    1000
2004    170    67450    1000
2005    858    67435    920

The calculation is (Minutes * Customers per Feeder) / total customers
So, for 2005 it would be (858 * 920)/67435 = 11.71

What I need is to calculate the moving average for 2005, 2004, 2003, etc.
Does this help?


Show quoteHide quote

There are OLAP functions coming in SQL:2005, but a quick way is to
build a table with year ranges in it:

CREATE TABLE YearRanges
(start_year INTEGER NOT NULL,
end_year INTEGER NOT NULL,
CHECK (start_year  - end_year = 2),  -- adjust as needed
PRIMARY KEY (start_year,  end_year));

Now the query is easy:

SELECT F.year_nbr, (SUM(minutes_down) * SUM( cust_feeder) ) / SUM
(cust_cnt)
  FROM Foobar AS F, YearRanges AS Y
WHERE F.year_nbr BETWEEN Y.start_year  AND Y.end_year;

Try providing more info as Aaron requested. For something based on guess
work, try a correlated subquery like:

SELECT ...., t1.dt,
       ( SELECT AVG( t2.dt )
           FROM tbl t2
          WHERE t2.dt BETWEEN DATEADD( year, -3, t1.dt ) AND t1.dt )
  FROM tbl t1 ;

Change a stored proc to a function
Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt