Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using SQL to average data within a certain range

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

610 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