Three month Average

  • 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/

  • 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


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • @ 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