• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Using SQL to average data within a certain range


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
batbertram
Asked:
batbertram
1 Solution
 
Kobe_LenjouCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now