Help me in Growth rate query.. Urgent!!!

[jayanth1808]

Hi...


I have prepared a database to input the volumes for each activity.... I want to prepare query to calculate growth rate of volumes for each team wise based on previous year's volume for two weeks...

Here is the example.. Assume that today is 04/23/2009, I want the database to go back to Apr - 2008 and calculate the difference in volume by day wise comparing with same day of week in 2008.

(if 04/23/09 is 4th day in 4th week, database should compare the these volumes with 4th day in 4th week of Apr - 2008 and do it for two weeks calculate growth rate and find the average of those growth rate by teamwise)

Once the growth rate is calculated, take last years volume for two weeks ahead... and multiple it by 1+ or - the growth rate to predict what volume is going to occur.


attaching the database...Help me with this....

 

[khawar]

Attached is a quick sample for you

There are two queries in database first one calculates year,month of year,week of month, day of week

and

second (ComparisonWithLastYear) shows you the results as required by you by getting amount for last year and calculation growth rate