SQL recursive query to find datewise count

Question

I am looking for a SQL query to get well_count for the whole year (date wise) based on an already existing date table.

Please find below table, code and the result I am looking for.

Table_1

Pad name Well_count Rig_release_date
------------------------------------
Pad A       1       1/28/2023
Pad B       4       5/4/2023
Pad C       7       6/10/2023
Pad D       3       4/10/2023

Table_1 image

Code I am looking for

Date    Well Count
5/1/2023    Select sum(Well_count) from Table_1 where Rig_release_date <  5/1/2023
5/2/2023    Select sum(Well_count) from Table_1 where Rig_release_date <  5/2/2023
5/3/2023    Select sum(Well_count) from Table_1 where Rig_release_date <  5/3/2023
5/4/2023    Select sum(Well_count) from Table_1 where Rig_release_date <  5/4/2024
5/5/2023    Select sum(Well_count) from Table_1 where Rig_release_date <  5/5/2024
5/6/2023    Select sum(Well_count) from Table_1 where Rig_release_date <  5/6/2024
.
.

Code I am looking for image

Result

Date    Well Count
-------------------
5/1/2023    4
5/2/2023    4
5/3/2023    4
5/4/2023    4
5/5/2023    8
5/6/2023    8

Result image

I am not sure how to do this in SQL Server, do I need to use a recursive CTE or sliding window function?

As of now I have built this code to get the recursive dates

WITH cte_numbers AS
(
    SELECT 
        @SD as Date_
        --@SD as n , (sum(Total_wells) from duc_schedule where Rig_release_date < n)

    UNION ALL

    SELECT    
        DATEADD(day, 1, Date_) 
    FROM    
        cte_numbers
    WHERE  
        Date_ < @ED
)
SELECT 
    *
FROM 
    cte_numbers
OPTION (maxrecursion 400);

Answer 1

For that most probably you would need "calendar table" to join to, such table is easy to create with recursive CTE:

;with consecutiveDays as (
    select cast('2023-05-01' as date) [day]
    union all
    select dateadd(d, 1, [day]) from consecutiveDays
    where [day] < cast('2023-06-30' as date)
)

Then, having your data represented as below:

declare @tbl table (
    PadName nvarchar(100),
    WellCount int,
    RigReleaseDate date
)

insert into @tbl values
('Pad A', 1, '2023-01-28'),
('Pad B', 4, '2023-05-04'),
('Pad C', 7, '2023-06-10'),
('Pad D', 3, '2023-04-10')

you can write such query to get desired results:

select 
    [day],
    (select sum(WellCount) from @tbl
    where RigReleaseDate < [day]) WellCount
from consecutiveDays