title: Rolling sum based on date (when dates are missing)
body: <p>You may be aware of rolling the results of an aggregate over a specific number of preceding rows. I.e.: how many hot dogs did I eat over the last 7 days</p>
  <pre><code>SELECT HotDogCount, 
         DateKey, 
         SUM(HotDogCount) OVER (ORDER BY DateKey ROWS 6 PRECEDING) AS HotDogsLast7Days
  FROM dbo.HotDogConsumption
  </code></pre>
  <p>Results:</p>
  <pre><code>+-------------+------------+------------------+
  | HotDogCount |  DateKey   | HotDogsLast7Days |
  +-------------+------------+------------------+
  |           3 | 09/21/2020 |                3 |
  |           2 | 9/22/2020  |                5 |
  |           1 | 09/23/2020 |                6 |
  |           1 | 09/24/2020 |                7 |
  |           1 | 09/25/2020 |                8 |
  |           4 | 09/26/2020 |               12 |
  |           1 | 09/27/2020 |               13 |
  |           3 | 09/28/2020 |               13 |
  |           2 | 09/29/2020 |               13 |
  |           1 | 09/30/2020 |               13 |
  +-------------+------------+------------------+
  </code></pre>
  <p>Now, the problem I am having is when there are gaps in the dates. So, basically, one day my intestines and circulatory system are screaming at me: &quot;What the heck are you doing, you're going to kill us all!!!&quot; So, I decide to give my body a break for a day and now there is no record for that day. When I use the &quot;ROWS 6 PRECEDING&quot; method, I will now be reaching back 8 days, rather than 7, because one day was missed.</p>
  <p>So, the question is, do any of you know how I could use the OVER clause to truly use a date value (something like &quot;DATEADD(day,-7,DateKey)&quot;) to determine how many previous rows should be summed up for a true 7 day rolling sum, regardless of whether I only ate hot dogs on one day or on all 7 days?</p>
  <p>Side note, to have a record of 0 for the days I didn't eat any hotdogs is not an option. I understand that I could use an array of dates and left join to it and do a</p>
  <pre><code>CASE WHEN Datekey IS NULL THEN 0 END
  </code></pre>
  <p>type of deal, but I would like to find out if there is a different way where the rows preceding value can somehow be determined dynamically based on the date.</p>