Calculate Weekly totals and should appear immediately after the corresponding week's data

45 Views Asked by At

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;

0

There are 0 best solutions below