Calculating average for 3 consecutive years

Question

Calculating average for 3 consecutive years

WannaBee

SSC Enthusiast

Points: 118
More actions
August 12, 2012 at 9:53 pm

#264966

Hi,
I have this data:
12/30/2012 0:00Carfordfocus0
12/30/2011 0:00Carfordfocus11
12/30/2010 0:00Carfordfocus7
12/30/2009 0:00Carfordfocus15
12/30/2008 0:00Carfordfocus6
12/29/2012 0:00Carfordfocus12
12/29/2011 0:00Carfordfocus44
12/29/2010 0:00Carfordfocus21
12/29/2009 0:00Carfordfocus6
12/29/2008 0:00Carfordfocus3
12/30/2012 0:00CarAudiTT8
12/30/2011 0:00CarAudiTT9
12/30/2010 0:00CarAudiTT11
12/30/2009 0:00CarAudiTT24
12/30/2008 0:00CarAudiTT7
I need to calculate the average of 3 consecutive years for each row so the out put should look like this:
field1 field2 field3
12/30/2012 0:00Carfordfocus06
12/30/2011 0:00Carfordfocus1111
12/30/2010 0:00Carfordfocus79.333333333
12/30/2009 0:00Carfordfocus1510.5
12/30/2008 0:00Carfordfocus66
12/29/2012 0:00Carfordfocus1225.66666667
12/29/2011 0:00Carfordfocus4423.66666667
12/29/2010 0:00Carfordfocus2110
12/29/2009 0:00Carfordfocus64.5
12/29/2008 0:00Carfordfocus33
12/30/2012 0:00CarAudiTT89.333333333
12/30/2011 0:00CarAudiTT914.66666667
12/30/2010 0:00CarAudiTT1114
12/30/2009 0:00CarAudiTT2415.5
12/30/2008 0:00CarAudiTT77
I'm grouping by field1,field2 and field3
I really need your good ideas guys..
Thank you so much!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

WannaBee (8/12/2012)
Hi,
I have this data:
12/30/2012 0:00Carfordfocus0
12/30/2011 0:00Carfordfocus11
12/30/2010 0:00Carfordfocus7
12/30/2009 0:00Carfordfocus15
12/30/2008 0:00Carfordfocus6
12/29/2012 0:00Carfordfocus12
12/29/2011 0:00Carfordfocus44
12/29/2010 0:00Carfordfocus21
12/29/2009 0:00Carfordfocus6
12/29/2008 0:00Carfordfocus3
12/30/2012 0:00CarAudiTT8
12/30/2011 0:00CarAudiTT9
12/30/2010 0:00CarAudiTT11
12/30/2009 0:00CarAudiTT24
12/30/2008 0:00CarAudiTT7
I need to calculate the average of 3 consecutive years for each row so the out put should look like this:
field1 field2 field3
12/30/2012 0:00Carfordfocus06
12/30/2011 0:00Carfordfocus1111
12/30/2010 0:00Carfordfocus79.333333333
12/30/2009 0:00Carfordfocus1510.5
12/30/2008 0:00Carfordfocus66
12/29/2012 0:00Carfordfocus1225.66666667
12/29/2011 0:00Carfordfocus4423.66666667
12/29/2010 0:00Carfordfocus2110
12/29/2009 0:00Carfordfocus64.5
12/29/2008 0:00Carfordfocus33
12/30/2012 0:00CarAudiTT89.333333333
12/30/2011 0:00CarAudiTT914.66666667
12/30/2010 0:00CarAudiTT1114
12/30/2009 0:00CarAudiTT2415.5
12/30/2008 0:00CarAudiTT77
I'm grouping by field1,field2 and field3
I really need your good ideas guys..
Thank you so much!!

Hi,

How did you find the following average?

12/30/2012 0:00Carfordfocus06

You have only one row for date 12/30/2012 and Car ford focus?, ...

Not well posted question.

A select of form like this one may help you

select field1,field2,avg(field3)

from Your_Table

where field1 > dateadd(yy,-3,getdate())

group by field1,field2

order by field1 desc

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT *

FROM #Temp t

CROSS APPLY (

SELECT AVGfield3 = AVG(field3*1.00) FROM #Temp s

WHERE s.field2 = t.field2

AND s.field1 IN ( t.field1, DATEADD(YY, -1, t.field1), DATEADD(YY, -2, t.field1) )

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

WannaBee SSC Enthusiast Points: 118 More actions · Answer 3

Thank you so much Chris 🙂

That works just perfect!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

WannaBee (8/14/2012)
Thank you so much Chris 🙂
That works just perfect!

You're welcome - thanks for the nice feedback 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden