Solved

Using SQL to average data within a certain range

Posted on 2011-03-22
1
292 Views
Last Modified: 2012-05-11

I have a number of date-stamped sales transactions each of which belongs to a store A, B, C, etc...

The earliest and latest transactions for each store can all be different but there will always be transactions for the whole period per store (e.g. for any store, if the first transaction is on 14/01/2011 and the last is on 20/03/2011 then there will be a full set of transactions for the whole period).

I want to look at the average growth in sales values between different periods e.g. from January 2011 to February 2011.

I'm sure you can see the problem:

To do this I must consider only transactions from stores that have an earliest transaction of 01/01/2011 or before and a latest transaction of 28/02/2011 or later.

In principle I could:

1) Look up each stores earliest and latest transactions
2) Add a conditional to my SQL query that includes only valid stores

The problem with this is that I have around 2000 stores and so my conditional could end up being, for example, 566 additional "AND StoreCode=" clauses which does not seem very efficient.

I would have thought that there would be a pattern to solve this problem as it must be a common concern in generating many like-for-like growth statistics?
0
Comment
Question by:batbertram
1 Comment
 
LVL 8

Accepted Solution

by:
Kobe_Lenjou earned 500 total points
ID: 35189506
Something like this?

Select avg(col1), shopID from salesTable M
where (select min(saleDate) from salestable where ShopID = M.ShopID) > "2010.01.01"
and  (select max(saleDate) from salestable where ShopID = M.ShopID) < "2010.01.31"
group by M.ShopID

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

813 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

13 Experts available now in Live!

Get 1:1 Help Now