Dynamic Calculations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Calculations

Author

Topic

WannaBee
Starting Member

2 Posts

Posted - 2012-08-10 : 12:00:15

Hi,

I have this data:

Date RawData
01/01/2012 8
01/01/2011 4
01/01/2010 12
01/01/2009 3
01/01/2008 2

What I need to do is dynamically calculate the average of RawData field for the last 3 years starting from the year mentioned in the row itself and 2 years back.

This is what I need:
Date RawData Calc
01/01/2012 8 8
01/01/2011 4 6.333333333
01/01/2010 12 5.666666667
01/01/2009 3 2.5
01/01/2008 2 2

for 2012: (8+4+12)/3=8
for 2011: (4+12+3)/3=6.333
for 2010: (12+3+2)/3=5.666
for 2009: (3+2)/2=2.5
for 2008: 2/1=2

Of course there would be 2013, 2014 etc that will upload to the table in the future.. so it should be good for future use..

I really need your good ideas guys..

Thank you so much!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 12:04:59


SELECT t.*,RunAvg AS Calc
FROM Table t
CROSS APPLY (SELECT AVG(RawData * 1.0) AS RunAvg
             FROM Table
             WHERE [Date]>=DATEADD(yy,DATEDIFF(yy,0,t.[Date])-3,0)
             AND [Date]<=t.[Date]
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WannaBee
Starting Member

2 Posts

Posted - 2012-08-10 : 13:02:47

Thank you very much!

I am really new to cross apply and of course my table is more complicated than this..

what if I have a lot of dates + 3 other "group by" fields? how do I apply that then?

Would really really appreciate a little more help..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:06:24

sorry not clear from your explanation. can you post some sample data to illustrate it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources