?
Solved

Need SQL staement for AVERAGE of pevious 3 months data

Posted on 2006-11-22
2
Medium Priority
?
237 Views
Last Modified: 2006-11-22
Hi
 have a sales table where I'd like to extract sales figure for the month, but also sales for figures for the AVERAGE of the 3 months prior to the month

eg;

MONTH      SALES        AVG. PREV 3 MONTH SALES
JAN 2006    50             n/a
FEB 2006    80             n/a
MAR 2006   30             n/a
APR 2006    70             53.3


thanks a lot for any help!
Fergal
0
Comment
Question by:fjkilken
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17995512


select month, sales, (select avg(b.sales) from yourtable b where month(b.date) between month(a.date) -3 and month(a.date) -1 and year(b.date) = year(a.date)) averagefor3month
from yourtable
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 1500 total points
ID: 17995516
correction

select a.month, a.sales, (select avg(b.sales) from yourtable b where month(b.date) between month(a.date) -3 and month(a.date) -1 and year(b.date) = year(a.date)) averagefor3month
from yourtable A
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question