?
Solved

Using SQL to average data within a certain range

Posted on 2011-03-22
1
Medium Priority
?
302 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 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

585 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