title: 7 day average between date range
body: <p>I am using SQL Server to solve the following question:</p>
  
  <p>My table T1 has the following data:</p>
  
  <pre><code>Date         Id   Name   Rent   Number
  01/01/2019    1    A      100    10
  01/02/2019    1    A      200    30
  01/03/2019    1    A      300    40
  .
  .
  .
  12/31/2019    1    A      150    25
  </code></pre>
  
  <p>The data exists for different combinations of Id and Name. I am trying to find average Rent and Number for 7 days:</p>
  
  <p>Final Output</p>
  
  <pre><code> Date                      Id   Name   Rent                   Number
   01/01/2019 - 01/07/2019   1    A      Avg(rent for 7 days)   Avg(Number for 7 days)
   01/08/2019 - 01/14/2019   1    A      Avg(rent for 7 days)   Avg(Number for 7 days)
  
  The final data should be grouped by Id and Name
  </code></pre>
  
  <p>My code:</p>
  
  <pre><code>SELECT min(date), Id, Name,
      AVG(Rent) as Rent,
      AVG(Number) Number,
      AVG(AVG(Rent)) OVER (ORDER BY min(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as AvgRent,
      AVG(AVG(Number)) OVER (ORDER BY min(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as AvgNumber
  FROM T1
  WHERE date &gt;= '2019-01-01'
  AND date &lt; '2019-12-31'
  GROUP BY Id, Name
  </code></pre>
  
  <p>My output has only row.</p>