Analytic function returns "wrong" values with AVG

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

Analytic function returns "wrong" values with AVG

Author

Topic

landau66
Yak Posting Veteran

61 Posts

Posted - 2014-02-04 : 11:43:32

Hello to all!

I have following select-statement:


select [date], [close], AVG([close]) over (order by [date] rows between 2  preceding and current row ) as [ma]
from dax2

My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "eroneous" values are not inserted or rather are shown as null.

Thank you very much
landau

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2014-02-04 : 13:13:31

Try this:


; WITH CTE AS
(select ROW_NUMBER() over(order by [date] ASC) as row, [date], 
     [close], AVG([close]) over (order by [date] rows between 2  preceding and current row ) as [ma]
      from dax2)
select date, (case when row > 2 then CTE.[ma] else null end) as [ma] from CTE;

landau66
Yak Posting Veteran

61 Posts

Posted - 2014-02-04 : 16:43:03

thank you very much mumu!

I thought there is an other way to achieve this. an easier one. Something Special in the way you write the Expression in the parenthesis after the over clause.

Maybe someone has heard of it. But maybe I am simply wrong.

At least I have a way now to achieve what i want!

Many greetings
landau

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources