June 24, 2011 at 8:17 am
create table #data(
name varchar(6),
[status] varchar(3),
[A] int,
int,
[C] int,
[Fordate] datetime
)
--drop table #data
insert into #data
select 'SIKOP','IMP',56,7,13,'2009-11-01 00:00:00.000'
union all
select 'SIKOP','IMP',3,2,0,'2009-12-01 00:00:00.000'
union all
select 'SIKOP','IMP',1,0,0,'2010-01-01 00:00:00.000'
union all
select 'SIKOP','IMP',0,0,0,'2010-02-01 00:00:00.000'
union all
select 'SIKOP','IMP',1,0,0,'2010-03-01 00:00:00.000'
union all
select 'SIKOP','IMP',10,1,2,'2010-04-01 00:00:00.000'
union all
select 'SIKOP','IMP',11,2,2,'2010-05-01 00:00:00.000'
union all
select 'SIKOP','IMP',2,4,0,'2010-06-01 00:00:00.000'
union all
select 'SIKOP','IMP',36,10,31,'2010-07-01 00:00:00.000'
union all
select 'SIKOP','IMP',80,45,33,'2010-08-01 00:00:00.000'
union all
select 'SIKOP','IMP',56,25,30,'2010-09-01 00:00:00.000'
union all
select 'SIKOP','IMP',87,34,35,'2010-10-01 00:00:00.000'
union all
select 'SIKOP','IMP',97,23,59,'2010-11-01 00:00:00.000'
union all
select 'SIKOP','IMP',96,15,52,'2010-12-01 00:00:00.000'
union all
select 'SIKOP','IMP',70,53,61,'2011-01-01 00:00:00.000'
union all
select 'SIKOP','IMP',91,42,60,'2011-02-01 00:00:00.000'
union all
select 'SIKOP','IMP',51,31,58,'2011-03-01 00:00:00.000'
union all
select 'SIKOP','IMP',47,8,60,'2011-04-01 00:00:00.000'
union all
select 'SIKOP','IMP',132,25,64,'2011-05-01 00:00:00.000'
--select * from #data
select [name], [status], fordate, [A],(select avg(b.[A])
from #data b
where a.fordate = b.fordate
and a.name = b.name
and [status] = 'IMP'
and datediff(mm,b.fordate,a.fordate) between 0 and 3) [3month]
from #data a
where [status] = 'IMP'
order by fordate
I am trying to have a running 3 month average for each line. It is calculated by adding the current months value for [A] + previous months value for [A] + 2 months prior value for [A] divided by 3.
For example; the line for 2010-01-01 the 3 month average should be 20 ((56+3+1)/3)
the line 2010-02-01; the 3 month average should be 1.333 ((3+1+0)/3)
and so on.
Any help on this is would be grand.
:-):-)
taybre
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2011 at 8:36 am
Something like this?
;WITH id_CTE AS (
SELECT name, status, fordate, a,
ROW_NUMBER() OVER (ORDER BY fordate ASC) AS id
FROM #data),
work_CTE AS (
SELECT name, status, fordate, a, id
FROM id_CTE
WHERE id = 1
UNION ALL
SELECT a.name, a.status, a.fordate, (a.a+b.a+c.a)/3.0 AS a, a.id
FROM id_CTE a
INNER JOIN id_CTE b ON a.id-1 = b.id
INNER JOIN id_CTE c ON a.id-2 = c.id)
SELECT name, status, fordate, a AS average FROM work_CTE
June 24, 2011 at 9:01 am
@ skcadavre
That worked like a dream.
Thank you
:-):-)
taybre
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply