?
Solved

Using SQL to average data within a certain range

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.
Suggested Courses

752 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