title: SQL Server window functions: can you do a ROW BETWEEN type construct?
body: <p>This is my version of SQL Server: </p>
  
  <blockquote>
    <p>Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64)   Feb 25 2011 13:56:11   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2) </p>
  </blockquote>
  
  <p>I want to do something like this (which works in Sybase):</p>
  
  <pre><code>SELECT AVG(ct) OVER (PARTITION BY val1 
                           ORDER BY val2 ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
  FROM table
  </code></pre>
  
  <p>Is there a way to implement something like this WITHOUT some nasty self join, or a subquery that uses row number etc? I suppose if I HAVE to do that I will, but I'd love to use the best most compact code.</p>
  
  <p>EDIT:<br>
  Per the comments, here is what I'd like to do.</p>
  
  <p>I have a <code>time_id</code> and a value. I want to take a moving average (1 week) of the value. Pretty simple.</p>