title: SQL 2008 Running average with group by
body: <p>So i have a table containing below columns.
  I want to compute an running average from positiondate and for example 3 days back, grouped on dealno. 
  I know how to do with "case by" but problem is that I have around 200 different DealNo so I do not want to write an own case by clause for every deal.</p>
  
  <p>On dealNo 1 it desired output should be Average(149 243 440 + 149 224 446 + 149 243 451)</p>
  
  <pre><code>DealNo      PositionDate    MarketValue
      1   |   2016-11-27  |   149 243 440
      2   |   2016-11-27  |   21 496 418
      3   |   2016-11-27  |   32 249 600
      1   |   2016-11-26  |   149 243 446
      2   |   2016-11-26  |   21 496 418
      3   |   2016-11-26  |   32 249 600
      1   |   2016-11-25  |   149 243 451
      3   |   2016-11-25  |   32 249 600
      2   |   2016-11-25  |   21 496 418
      3   |   2016-11-24  |   32 249 600
      1   |   2016-11-24  |   149 225 582
      2   |   2016-11-24  |   21 498 120
      1   |   2016-11-23  |   149 256 867
      2   |   2016-11-23  |   21 504 181
      3   |   2016-11-23  |   32 253 440
      1   |   2016-11-22  |   149 256 873
      2   |   2016-11-22  |   21 506 840
      3   |   2016-11-22  |   32 253 440
      1   |   2016-11-21  |   149 234 535
      2   |   2016-11-21  |   21 509 179
      3   |   2016-11-21  |   32 253 600
  </code></pre>
  
  <p>I tried below script but it was not very effective since my table contains around 300k rows and approx 200 different dealno.
  Is there a more effective way to do this in SQL 2008?</p>
  
  <pre><code>with cte as (
  
  SELECT ROW_NUMBER() over(order by dealno, positiondate desc) as Rownr,
          dealno,
          positiondate,
          Currency,
          MvCleanCcy
    FROM T1
             )
  
  select 
  rownr, positiondate, DealNo, Currency,
  mvcleanavg30d = (select avg(MvCleanCcy) from cte2 where Rownr between c.Rownr and c.Rownr+3)
  
   from cte as c
  </code></pre>