I have a data like below
| contact_date | count | values |
|---|---|---|
| 06/01/2023 | 230 | 0 |
| 06/02/2023 | 100 | 0 |
| 06/03/2023 | 200 | 0 |
| 06/04/2023 | 250 | 0 |
| 06/05/2023 | 120 | 0 |
| 06/06/2023 | 120 | 0 |
| 06/07/2023 | 200 | 0 |
| 06/08/2023 | 2230 | 0 |
| 06/09/2023 | 2310 | 0 |
| 06/10/2023 | 30 | 0 |
| 06/11/2023 | 300 | 0 |
| 06/12/2023 | 100 | 0 |
| 06/13/2023 | 120 | 0 |
| 06/14/2023 | 790 | 0 |
| 06/15/2023 | 900 | 0 |
and I want to calculate the weekly totals for the count and values and display the weekly total right after week. Weeks start Sunday and ends on Saturday. technically it should look like below
| contact_date | count | values |
|---|---|---|
| 06/01/2023 | 230 | 0 |
| 06/02/2023 | 100 | 0 |
| 06/03/2023 | 200 | 0 |
| Weektot | 530 | 0 |
| 06/04/2023 | 250 | 0 |
| 06/05/2023 | 120 | 0 |
| 06/06/2023 | 120 | 0 |
| 06/07/2023 | 200 | 0 |
| 06/08/2023 | 2230 | 0 |
| 06/09/2023 | 2310 | 0 |
| Weektot | 5260 | 0 |
| 06/10/2023 | 30 | 0 |
| 06/11/2023 | 300 | 0 |
| 06/12/2023 | 100 | 0 |
| 06/13/2023 | 120 | 0 |
| 06/14/2023 | 790 | 0 |
| 06/15/2023 | 900 | 0 |
| Weektot | value | 0 |
Here is the code I have written, I was able to calculate at the weekly level but I am not able to order it correctly. All the week data is appending at the end of the result
WITH WeeklyData AS (
SELECT
contact_date,
count,
values,
DATEPART(WEEK, contact_date) AS week_number
FROM table
)
SELECT
contact_date,
count,
values
FROM WeeklyData
UNION ALL
SELECT
'Week',
SUM(count),
SUM(values)
FROM WeeklyData
GROUP BY week_number
ORDER BY contact_date;