title: Rolling Running total over last 7 days by date and not bound by rows preceding but rather dates preceding for each row in a SQL Table
body: <p>I am struggling with a specific MS SQL Calculation.</p>
  
  <p>I am trying to calculate the running total of the last 7 days for each date. However, there are sometimes gaps in the data so using a window function is proving difficult. I do not wish to sum the last 7 rows or entries, but rather sum by date.</p>
  
  <p>As an example, the row with the date ‘2017-01-30’ should sum up itself and the preceding rows for ‘2017-01-24’ only, as that falls within the last 7 days.</p>
  
  <p>I have tried several ways of coding this using a windowed function and also trying to left join this to a calendar table (that has all date in) but the gaps will still remain.</p>
  
  <p>It is causing me a big headache as I am 100% sure that I am missing something - surely there is a simple way of doing this that I cannot figure out???</p>
  
  <p>The data is in a table that has an ID column (bigint), date column (date) and number column (money).</p>
  
  <p>Data sample (ID is excluded, it is not needed as it is non-unique and nullable):</p>
  
  <pre><code>DATE            NUMBER
  2017-01-01      8.5215
  2017-01-02      17.043
  2017-01-05      8.5361
  2017-01-09      8.6401
  2017-01-09      17.2801
  2017-01-16      17.6041
  2017-01-19      8.652
  2017-01-24      17.1984
  2017-01-24      8.5992
  2017-01-30      8.5317
  2017-02-01      8.5536
  2017-02-02      8.5463
  2017-02-06      8.6222
  2017-02-06      17.2444
  2017-02-13      16.988
  2017-02-14      8.4796
  2017-02-21      21.2206
  2017-02-27      8.5106
  2017-02-28      8.5222
  </code></pre>
  
  <p>Example of result set required:</p>
  
  <p><img src="https://i.stack.imgur.com/J41y3.png" alt="maths example"></p>
  
  <p>If any MS SQL Geniuses could help to write a little piece of code to save my life I’d be really grateful!</p>
  
  <p>Thanks so much,</p>
  
  <p>Shaun</p>