Solved

Need SQL staement for AVERAGE of pevious 3 months data

Posted on 2006-11-22
2
229 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
  • 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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now