Calcuate 20 days Average - 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

Calcuate 20 days Average

Author

Topic

OWSQL
Starting Member

27 Posts

Posted - 2011-10-28 : 04:21:33

Hi
I need to calculate a 20 day average on 6 priceindex described as below:
60% of 20 days avg (priceindex 1 + priceindex 2)
20% of 20 days avg (priceindex 3 + priceindex 4)
20% of 20 days avg (priceindex 5 + priceindex 6)

The columns I will use is priceindex, pricedate and price.
Normally I would do it the hard way using temp tables and then firste retrive 20 days hist values for eacj index and then reprocess it from there.

But there must be a smarter way, I think. Any good suggestions?
Thanks
Steen

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-28 : 04:30:18

To make it easier for us to help you should give:
sample table structure
sample data
wanted result in relation to the sample data

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 04:51:06

do you mean this?


SELECT t.priceindex, t.pricedate,t.price,
t1.RunAvg *  CASE WHEN t.priceindex IN (1,2) THEN 0.6
     WHEN t.priceindex IN (3,4,5,6) THEN 0.2
    END AS AvgVal
FROM table t
CROSS APPLY (SELECT AVG(price*1.0) AS RunAvg
             FROM table 
             WHERE priceindex=t.priceindex
             AND pricedate >= DATEADD(dd,DATEDIFF(dd,0,t.pricedate),-19) AND pricedate < DATEADD(dd,DATEDIFF(dd,0,t.pricedate),1)
             ORDER BY pricedate  DESC)t1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources