7 day average between date range

Question

I am using SQL Server to solve the following question:

My table T1 has the following data:

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

The data exists for different combinations of Id and Name. I am trying to find average Rent and Number for 7 days:

Final Output

 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

My 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 >= '2019-01-01'
AND date < '2019-12-31'
GROUP BY Id, Name

My output has only row.

Answer 1

You need to group the dates by week which you can do by finding the difference between '01/01/2019' and your Date column using the DATEDIFF function and then divide it by 7. Since both the dividend and the divisor are integers the quotient will be an integer as well with the effect of group your dates into weeks.

SELECT   MIN(Date)   AS [Start Date]
       , MAX(Date)   AS [End Date]
       , Id
       , Name
       , AVG(Rent)   AS [Avg Rent]
       , AVG(Number) AS [Avg Number]
FROM     T1
GROUP BY DATEDIFF(DAY, '01/01/2019', Date) / 7;