How to add data constantly to a table from a query

Question

I have a table with individual transactions. Each row represents one car wash.
I would like to know how many washes I have done in the past 365 days.

This is very simple:

Select sum(st.net_sum) as revenue
from dbo.sales_transactions as st 
where st.date BETWEEN adddate(day, -366, getdate()) AND getdate()

However, I would like to see this query for each day of the past 365 days. If I do this e.g. as an iterated query, the query takes a very long time (in my case over 30 seconds, because I also have to work with some joins).

So I would like to do this query once a day and save the output "revenue" in a new table.

How to do this so that this is updated daily? Do I have to write a PHP script that is called daily or can I do this directly in MSSQL?

The original query for number of washes is:

DECLARE @start_date DATE = '2022-03-29';
DECLARE @end_date DATE = '2023-03-29';

WITH AllDays
AS ( 
    SELECT   @start_date AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM AllDays
    WHERE [Date] < @end_date
)
,   WashData as (
        SELECT
            count(st.date) as NumberOfWashes,
            cast(st.date as date) as DayOfWashes
        FROM 
            POS.dbo.sales_transaction_line_item as stli
            join POS.dbo.sales_transaction as st on st.sales_transaction_id = stli.fk_sales_transaction
            join POS.dbo.sales_item as si on si.sales_item_id = stli.fk_sales_item
        WHERE
            st.fk_sales_status <> 3
            and si.fk_sales_item_type = 1
            and st.date BETWEEN @start_date and @end_date
        Group by
            cast(st.date as date)
    )
SELECT 
    [Date]
    ,NumberOfWashes
    ,sum(NumberofWashes) over (order by [Date] rows between 365 preceding AND current row) as Window365
FROM   AllDays ad
left join WashData wd on ad.[Date] = wd.DayOfWashes
ORDER BY [Date] desc
OPTION (MAXRECURSION 0)

This takes not much time: 00:00:00.2413543

The query for Sum of Revenue however takes much longer:

DECLARE @start_date DATE = '2022-03-29';
DECLARE @end_date DATE = '2023-03-29';

WITH AllDays
AS (
    SELECT   @start_date AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM AllDays
    WHERE [Date] < @end_date
)
,   WashData as (
        SELECT
            sum(st.sum_net) as NumberOfWashes,
            cast(st.date as date) as DayOfWashes
        FROM 
            POS.dbo.sales_transaction_line_item as stli
            join POS.dbo.sales_transaction as st on st.sales_transaction_id = stli.fk_sales_transaction
            join POS.dbo.sales_item as si on si.sales_item_id = stli.fk_sales_item
        WHERE
            st.fk_sales_status <> 3
            and si.fk_sales_item_type = 1
            and st.fk_payment_method <> 4
            and st.date BETWEEN @start_date and @end_date
        Group by
            cast(st.date as date)
    )
SELECT 
    [Date]
    ,NumberOfWashes
    ,sum(NumberofWashes) over (order by [Date] rows between 365 preceding AND current row) as Window365
FROM   AllDays ad
left join WashData wd on ad.[Date] = wd.DayOfWashes
ORDER BY [Date] desc
OPTION (MAXRECURSION 0)

That takes much longer: 00:01:19.7617031