Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    Davidson, NC
    Posts
    2

    Talking Unanswered: Rolling Averages

    Deeply interested in suggested SQL to compute 3 year rolling averages of the count of records in a recordset. Have a date field. Help.

    Rich

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    select RA3Y_Cnt=AVG(Cnt),[Year]=y.[Year]+x.x
    from (select Cnt=count(*),[Year]=year(DateCol) from viData group by year(DateCol)) y
    cross join (select "x"=-1 union all select "x"=0 union all select "x"=1) x
    group by y.[Year]+x.x
    order by y.[Year]+x.x

  3. #3
    Join Date
    Mar 2003
    Location
    Davidson, NC
    Posts
    2
    Awesome! Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •