How to update column with average weekly value for each day in sql

Question

I have the following table. I insert a column named WeekValue, I want to fill the weekvalue column with the weekly average value of impressionCnt of the same category for each row.
Like:

+-------------------------+----------+---------------+--------------+
|          Date           | category | impressioncnt | weekAverage  |
+-------------------------+----------+---------------+--------------+
| 2014-02-06 00:00:00.000 | a        |           123 |          100 |
| 2014-02-06 00:00:00.000 | b        |           121 |          200 |
| 2014-02-06 00:00:00.000 | c        |           99  |          300 |
| 2014-02-07 00:00:00.000 | a        |           33  |          100 |
| 2014-02-07 00:00:00.000 | b        |           456 |          200 |
| 2014-02-07 00:00:00.000 | c        |           54  |          300 |
| 2014-02-08 00:00:00.000 | a        |           765 |          100 |
| 2014-02-08 00:00:00.000 | b        |           78  |          200 |
| 2014-02-08 00:00:00.000 | c        |           12  |          300 |
| .....                   |          |               |              |
| 2014-03-01 00:00:00.000 | a        |           123 |          111 |
| 2014-03-01 00:00:00.000 | b        |           121 |          222 |
| 2014-03-01 00:00:00.000 | c        |           99  |          333 |
| 2014-03-02 00:00:00.000 | a        |           33  |          111 |
| 2014-03-02 00:00:00.000 | b        |           456 |          222 |
| 2014-03-02 00:00:00.000 | c        |           54  |          333 |
| 2014-03-03 00:00:00.000 | a        |           765 |          111 |
| 2014-03-03 00:00:00.000 | b        |           78  |          222 |
| 2014-03-03 00:00:00.000 | c        |           12  |          333 |
+-------------------------+----------+---------------+--------------+

I tried

update [dbo].[RetailTS]
set Week = datepart(day, dateDiff(day, 0, [Date])/7 *7)/7 +1

To get the week numbers then try to group by the week week number and date and category, but this seems isn't correct. How do I write the SQL query? Thanks!

Answer 1

Given that you may be adding more data in the future, thus requiring another update, you might want to just select out the weekly averages:

SELECT
    Date,
    category,
    impressioncnt,
    AVG(impressioncnt) OVER
        (PARTITION BY category, DATEDIFF(d, 0, Date) / 7) AS weekAverage
FROM RetailTS
ORDER BY
    Date, category;