title: Select time windows and sliding time windows
body: <p>I'm looking for a solution with good performance for my following problem:</p>
  
  <p>I have a two tables</p>
  
  <ol>
  <li>Event-Table containing a event and the date of this event</li>
  <li>Temperature-Table containing a date (sequential over 365 days beginning bye 2015-01-01) and a value</li>
  </ol>
  
  <p>The first I want to do is to select all values 5 days or less days before a event into a separate table and also fill the columns for eventNumber and daysBeforeEvent</p>
  
  <pre><code>| eventNumber| dayBeforeEv | value        |
  |:-----------|------------:|:------------:|
  | 1          | 1           |     -1       |
  | 1          | 2           |     21       |
  | 1          | 3           |     15       |
  | 1          | 4           |     7        |
  | 1          | 5           |     -7       |
  | 2          | 1           |     -9       |
  | 2          | 2           |     12       |
  | 2          | 3           |     1        |
  | 2          | 4           |     -7       |
  | 2          | 5           |     8        |
  | 3          | 1           |     18       |
  | ...        | ...         |     ...      |
  </code></pre>
  
  <p>In a second table I want to select all values for the time between events in same structure. Just I want to use here a kind of sliding window.</p>
  
  <pre><code>Day     |1  |2  |3  |4  |5  |6  |7  |8  |9  |10 |
  Event   |X  |   |   |   |   |   |   |   |   |X  |
  </code></pre>
  
  <p>First window will be from day 2 to day 6
  Then the window will be moved by one day and the second window will be from day 3 to day 7 etc.
  if there will be an event the window will become shorter e.g from day 6 to day 9 (4 days, next one will include only 3 days)</p>
  
  <p>I use now following solution:</p>
  
  <pre><code>DECLARE @cnt INT = 1;
  
  WHILE @cnt &lt;= 5
  BEGIN
  
  INSERT INTO "RESULT" ("EVENT_NUM","EVENT_ID","VALUE","DATE") 
  SELECT  ROW_NUMBER() OVER (ORDER BY "DATE" ASC) AS ROW,@cnt,"VALUE","DATE" FROM "TEMP_TABLE" 
  WHERE "DATE" IN (SELECT DATEADD(day,@cnt*-1,"DATE") FROM "EVENT" ORDER BY "DATE" ASC);
  
  SET @cnt = @cnt + 1;
  
  END;
  </code></pre>
  
  <p>It works fine until there are no dates missing in the TEMP_Table. But when there is a gap (e.g I have entry on 2016-02-01 and 2016-02-03 so the 2016-02-02 is missing)</p>