moving 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

SQL Server 2000 Forums

Transact-SQL (2000)

moving average

Author

Topic

pcock
Starting Member

12 Posts

Posted - 2009-01-08 : 00:04:12

Happy new year all!

I'm not so sure how I can do this in TSQL. I'd like to find the moving average for last five transactions (for each customer) and write it to the 6th field on every row.


customer_id	purchase_date	amount	mov_avg
1001	1/10/2008	18.8	
1001	2/10/2008	227.07	
1001	3/10/2008	202.9	
1001	4/10/2008	218.94	
1001	5/10/2008	92.8	
1001	6/10/2008	378.33	152.10
1001	7/10/2008	351.87	224.01
1001	8/10/2008	437.01	248.97
1001	9/10/2008	169.64	295.79
1001	10/10/2008	411.2	285.93
1001	11/10/2008	423.61	349.61
2002	1/10/2008	410.17	
2002	2/10/2008	37.8	
2002	3/10/2008	244.86	
2002	4/10/2008	244.1	
2002	5/10/2008	227.01	
2002	6/10/2008	169.95	232.79
2002	7/10/2008	432.32	184.74
2002	8/10/2008	457.42	263.65
2002	9/10/2008	495.79	306.16
2002	10/10/2008	65.95	356.50
2002	11/10/2008	313.36	324.29

Can someone please help me out? I'm using excel to do this and it's taking a whole lot of time :(

Thanks

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-08 : 14:04:08

You can calculate the average for each row and in a second pass eliminate the results for all rows not multiples of 6.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-08 : 15:50:44

Something like this ???

select t.a,t.b,isnull(avg(t1.c) ,0)
from 
(
select '1001' a,convert(datetime,'1/10/2008',103) b,	18.8	c union
select '1001',	convert(datetime,'2/10/2008',103) ,	227.07	union
select '1001',	convert(datetime,'3/10/2008',103),	202.9	union
select '1001',	convert(datetime,'4/10/2008',103),	218.94	union
select '1001',	convert(datetime,'5/10/2008',103),	92.8	union
select '1001',	convert(datetime,'6/10/2008',103),	378.33	union
select '1001',	convert(datetime,'7/10/2008',103),	351.87	union
select '1001',	convert(datetime,'8/10/2008',103),	437.01	union
select '1001',	convert(datetime,'9/10/2008',103),	169.64	union
select '1001',	convert(datetime,'10/10/2008',103),	411.2	union
select '1001',	convert(datetime,'11/10/2008',103),	423.61	union
select '2002',	convert(datetime,'1/10/2008',103),	410.17	union
select '2002',	convert(datetime,'2/10/2008',103),	37.8	union
select '2002',	convert(datetime,'3/10/2008',103),	244.86	union
select '2002',	convert(datetime,'4/10/2008',103),	244.1	union
select '2002',	convert(datetime,'5/10/2008',103),	227.01	union
select '2002',	convert(datetime,'6/10/2008',103),	169.95	union
select '2002',	convert(datetime,'7/10/2008',103),	432.32	union
select '2002',	convert(datetime,'8/10/2008',103),	457.42	union
select '2002',	convert(datetime,'9/10/2008',103),	495.79	union
select '2002',	convert(datetime,'10/10/2008',103),	65.95	union
select '2002',	convert(datetime,'11/10/2008',103),	313.36	


)t
left join 
(
select '1001' a,convert(datetime,'1/10/2008',103) b,	18.8	c union
select '1001',	convert(datetime,'2/10/2008',103) ,	227.07	union
select '1001',	convert(datetime,'3/10/2008',103),	202.9	union
select '1001',	convert(datetime,'4/10/2008',103),	218.94	union
select '1001',	convert(datetime,'5/10/2008',103),	92.8	union
select '1001',	convert(datetime,'6/10/2008',103),	378.33	union
select '1001',	convert(datetime,'7/10/2008',103),	351.87	union
select '1001',	convert(datetime,'8/10/2008',103),	437.01	union
select '1001',	convert(datetime,'9/10/2008',103),	169.64	union
select '1001',	convert(datetime,'10/10/2008',103),	411.2	union
select '1001',	convert(datetime,'11/10/2008',103),	423.61	union
select '2002',	convert(datetime,'1/10/2008',103),	410.17	union
select '2002',	convert(datetime,'2/10/2008',103),	37.8	union
select '2002',	convert(datetime,'3/10/2008',103),	244.86	union
select '2002',	convert(datetime,'4/10/2008',103),	244.1	union
select '2002',	convert(datetime,'5/10/2008',103),	227.01	union
select '2002',	convert(datetime,'6/10/2008',103),	169.95	union
select '2002',	convert(datetime,'7/10/2008',103),	432.32	union
select '2002',	convert(datetime,'8/10/2008',103),	457.42	union
select '2002',	convert(datetime,'9/10/2008',103),	495.79	union
select '2002',	convert(datetime,'10/10/2008',103),	65.95	union
select '2002',	convert(datetime,'11/10/2008',103),	313.36	
)t1
on  (t1.b=t.b-1 or t1.b=t.b-2 or t1.b=t.b-3 or t1.b=t.b-4 or t1.b=t.b-5  )
and t.a=t1.a
group by 
t.a,t.b
order by 1,2

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-08 : 16:06:18

slightly modified, since you don't need averages for all rows

select t.a,t.b,case when count(t1.a)<5 then  null  else isnull(avg(t1.c) ,0) end
from 
(
select '1001' a,convert(datetime,'1/10/2008',103) b,	18.8	c union
select '1001',	convert(datetime,'2/10/2008',103) ,	227.07	union
select '1001',	convert(datetime,'3/10/2008',103),	202.9	union
select '1001',	convert(datetime,'4/10/2008',103),	218.94	union
select '1001',	convert(datetime,'5/10/2008',103),	92.8	union
select '1001',	convert(datetime,'6/10/2008',103),	378.33	union
select '1001',	convert(datetime,'7/10/2008',103),	351.87	union
select '1001',	convert(datetime,'8/10/2008',103),	437.01	union
select '1001',	convert(datetime,'9/10/2008',103),	169.64	union
select '1001',	convert(datetime,'10/10/2008',103),	411.2	union
select '1001',	convert(datetime,'11/10/2008',103),	423.61	union
select '2002',	convert(datetime,'1/10/2008',103),	410.17	union
select '2002',	convert(datetime,'2/10/2008',103),	37.8	union
select '2002',	convert(datetime,'3/10/2008',103),	244.86	union
select '2002',	convert(datetime,'4/10/2008',103),	244.1	union
select '2002',	convert(datetime,'5/10/2008',103),	227.01	union
select '2002',	convert(datetime,'6/10/2008',103),	169.95	union
select '2002',	convert(datetime,'7/10/2008',103),	432.32	union
select '2002',	convert(datetime,'8/10/2008',103),	457.42	union
select '2002',	convert(datetime,'9/10/2008',103),	495.79	union
select '2002',	convert(datetime,'10/10/2008',103),	65.95	union
select '2002',	convert(datetime,'11/10/2008',103),	313.36	


)t
left join 
(
select '1001' a,convert(datetime,'1/10/2008',103) b,	18.8	c union
select '1001',	convert(datetime,'2/10/2008',103) ,	227.07	union
select '1001',	convert(datetime,'3/10/2008',103),	202.9	union
select '1001',	convert(datetime,'4/10/2008',103),	218.94	union
select '1001',	convert(datetime,'5/10/2008',103),	92.8	union
select '1001',	convert(datetime,'6/10/2008',103),	378.33	union
select '1001',	convert(datetime,'7/10/2008',103),	351.87	union
select '1001',	convert(datetime,'8/10/2008',103),	437.01	union
select '1001',	convert(datetime,'9/10/2008',103),	169.64	union
select '1001',	convert(datetime,'10/10/2008',103),	411.2	union
select '1001',	convert(datetime,'11/10/2008',103),	423.61	union
select '2002',	convert(datetime,'1/10/2008',103),	410.17	union
select '2002',	convert(datetime,'2/10/2008',103),	37.8	union
select '2002',	convert(datetime,'3/10/2008',103),	244.86	union
select '2002',	convert(datetime,'4/10/2008',103),	244.1	union
select '2002',	convert(datetime,'5/10/2008',103),	227.01	union
select '2002',	convert(datetime,'6/10/2008',103),	169.95	union
select '2002',	convert(datetime,'7/10/2008',103),	432.32	union
select '2002',	convert(datetime,'8/10/2008',103),	457.42	union
select '2002',	convert(datetime,'9/10/2008',103),	495.79	union
select '2002',	convert(datetime,'10/10/2008',103),	65.95	union
select '2002',	convert(datetime,'11/10/2008',103),	313.36	
)t1
on  (t1.b=t.b-1 or t1.b=t.b-2 or t1.b=t.b-3 or t1.b=t.b-4 or t1.b=t.b-5  )
and t.a=t1.a
group by 
t.a,t.b
--having count(t1.a)=5
order by 1,2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources