CODEWITHSUNDEEP
sqlms-access
Alex Crisafi
Alex Crisafi

Reputation: 47

SQL Query in MS ACCESS to calculate averaged days depending on multiple criteria

I have the following table in MS Access 2007:

customer | Promotion |  Month   | activator | request_date | activation_date
   1     |   promo1  |   10     |   shop1   |  11/10/2011  |   21/10/2011  
   2     |   promo2  |   9      |   shop1   |  10/09/2011  |   15/09/2011  
   3     |   promo2  |   9      |   shop2   |  10/09/2011  |   16/09/2011
   4     |   promo1  |   10     |   shop1   |  12/10/2011  |   13/10/2011

What I need is a query to calculate the average number of days that each shop takes to activate each promotion grouped by month. So for example one result would be:

shop1 in October took an average of 10+1/2 days to activate promo1.

Thanks in advance!

Upvotes: 0

Views: 609

Answers (2)

user009
user009

Reputation: 1

Try this:

select 

  activator,
   [month], 
   promotion, 
   avg(convert( float, datediff(DAY, request_date,activation_date))) as avgTime

from dbo.Table1

group by activator,[month], promotion

Upvotes: 0

Marc B
Marc B

Reputation: 360762

SELECT activator, Month, Promotion, AVG(activation_date - request_date)
FROM ...
GROUP BY activator, Month, Promotion

Upvotes: 1

Related Questions