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.
| Author |
Topic |
|
pecusio
Starting Member
5 Posts |
 Posted - 2009-11-18 : 07:34:44
|
| Hello People I need to calculate a rolling average and I don't know how to do it. Let me explain myself better I have data that look like thisCOMPANY ANALYST DATE FORECAST A 1 2009/11/01 1 A 2 2009/11/02 2 A 3 2009/11/03 1 A 4 2009/11/05 3 B 1 2009/11/02 10 B 2 2009/11/01 11 B 3 2009/11/03 10 B 4 2009/11/04 9Now I want to calculate the average for for each analyst just taking into account the forecast made by any analyst up to that date for each company, something that looks likeCOMPANY ANALYST DATE AVG_FORECAST A 1 2009/11/01 1 A 2 2009/11/02 1.5 A 3 2009/11/03 1.33 A 4 2009/11/05 1.75 B 2 2009/11/01 11 B 1 2009/11/02 10.5 B 3 2009/11/03 10.33 B 4 2009/11/04 10I tried to do it using Temporal table but I didn't managed.Thanks, |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-11-18 : 07:37:49
|
[code]select t.*, a.AVG_FORECASTfrom table1 t cross apply ( select avg(FORECAST) as AVG_FORECAST from table1 x where x.COMPANY = t.COMPANY and x.ANALYST_DATE <= t.ANALYST_DATE ) a[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
pecusio
Starting Member
5 Posts |
Posted - 2009-11-18 : 11:10:44
|
| Thanks, Khtan solution did the job, Any article that explains this "cross apply" better? So i can better understand what is doing |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-11-19 : 01:27:39
|
quote:
Originally posted by pecusio
Thanks, Khtan solution did the job, Any article that explains this "cross apply" better? So i can better understand what is doing
Try google or bingMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Avarage