how to calculate 3 days average

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to calculate 3 days average

Author

Topic

the_justin
Starting Member

19 Posts

Posted - 2010-02-25 : 22:47:35

Hi all,

lets say i have a month data, and i need to calculate the 3 days average for it. here is the illustration:

date data

1/jan 1

2/jan 2

3/jan 4

4/jan 4

5/jan 6

6/jan 6

.

.

31/jan 4

and this is how i want the result to be

date data 3-days-avg

1/jan 1 0

2/jan 2 0

3/jan 4 0

4/jan 4 2.3 (1+2+4/3)

5/jan 6 3.3 (2+4+4/3)

6/jan 6 5.3 (6+6+4/3)

etc...

any idea of how can i produce that ?

thanks in advance

ps.hope my illustration is easy to understand.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-25 : 23:05:35

[code]
select *
from yourtable s
outer apply
(
select moving_avg = avg(data)
from (
select top 3 data
from yourtable x
where x.[date] <= s.[date]
order by x.[date] desc
) x
having count(*) = 3
) a
[/code]

KH
[spoiler]Time is always against us[/spoiler]

the_justin
Starting Member

19 Posts

Posted - 2010-02-26 : 00:34:21

thanks so much khtan....you're DA-MAN !!!!!

quote:

Originally posted by khtan


select	*
from	yourtable s
	outer apply
	(
		select	moving_avg = avg(data)
		from	(
				select	top 3 data
				from	yourtable x
				where	x.[date] <= s.[date]
				order by x.[date] desc
			) x
		having count(*) = 3
	) a

KH
[spoiler]Time is always against us[/spoiler]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 16:08:21

quote:

Originally posted by khtan


select	*
from	yourtable s
	outer apply
	(
		select	moving_avg = avg(data)
		from	(
				select	top 3 data
				from	yourtable x
				where	x.[date] <= s.[date]
				order by x.[date] desc
			) x
		having count(*) = 3
	) a

KH
[spoiler]Time is always against us[/spoiler]

declare @t table (date int, data int) insert @t values (1,1),(2,2),(3,4),(4,4),(5,6),(6,6)

;WITH Ranking AS
(SELECT *, 
        row_id = (SELECT COUNT(*)
                    FROM @t T2
                   WHERE  T2.[date] <= T1.[date])
   FROM @t T1)

SELECT date,
       data,      
       moving_avg = COALESCE((SELECT AVG(data + 0.0)
                                FROM Ranking R
                               WHERE R.row_id BETWEEN T.row_id - 3 
                                 AND T.row_id - 1
                              HAVING COUNT(*) = 3), 0)
  FROM Ranking T;
/*
date        data        moving_avg
----------- ----------- ---------------------------------------
1           1           0.000000
2           2           0.000000
3           4           0.000000
4           4           2.333333
5           6           3.333333
6           6           4.666666
*/

 
select	*
from	@t s
	outer apply
	(
		select	moving_avg = avg(data)
		from	(
				select	top 3 data
				from	@t x
				where	x.[date] <= s.[date]
				order by x.[date] desc
			) x
		having count(*) = 3
	) a
/*
date        data        moving_avg
----------- ----------- -----------
1           1           NULL
2           2           NULL
3           4           2
4           4           3
5           6           4
6           6           5
*/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 16:09:41

quote:
Originally posted by the_justin

and this is how i want the result to be

date data 3-days-avg

1/jan 1 0

2/jan 2 0

3/jan 4 0

4/jan 4 2.3 (1+2+4/3)

5/jan 6 3.3 (2+4+4/3)

6/jan 6 ~~5.3 (6+6+4/3)~~ 4.666 (4+4+6)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources