Daily status using prior value as backfill

This is the input table (tbl_statuslog):

User_id	isactive	date
1	1	1 Feb 2021
2	1	1 Feb 2021
3	1	2 Feb 2021
2	0	5 Feb 2021
4	1	10 Feb 2021
4	0	10 Feb 2021
3	0	12 Feb 2021

create table tbl_statuslog
(
  [user_id] int,
  [isactive] bit,
  [date] datetime
);

insert into tbl_statuslog (user_id, isactive, date) values
(1, 1, ' 1 Feb 2021'),
(2, 1, ' 1 Feb 2021'),
(3, 1, ' 2 Feb 2021'),
(2, 0, ' 5 Feb 2021'),
(4, 1, '10 Feb 2021'),
(4, 0, '10 Feb 2021'),
(3, 0, '12 Feb 2021');

I want to return output, given today is 16 Feb:

User_id	isactive	date
1	1	1 Feb 2021
.
.
.
1	1	16 Feb 2021
2	1	1 Feb 2021
.
.
2	1	4 Feb 2021
2	0	5 Feb 2021
.
.
2	0	16 Feb 2021
3	1	2 Feb 2021
.
.
3	1	11 Feb 2021
3	0	12 Feb 2021
.
.
3	0	16 Feb 2021
4	0	10 Feb 2021
.
.
4	0	16 Feb 2021

I have used following SQL to get the list of all dates.

    DECLARE @StartDateTime DATETIME
    DECLARE @EndDateTime DATETIME
    
    SET @StartDateTime = '2021-02-01'
    SET @EndDateTime = GETDATE();
    
    WITH DateRange(DateData) AS 
    (
        SELECT @StartDateTime as Date
        UNION ALL
        SELECT DATEADD(d,1,DateData)
        FROM DateRange 
        WHERE DateData < @EndDateTime
    )
    SELECT DateData
    FROM DateRange
    OPTION (MAXRECURSION 0)
    GO

Now I am thinking of doing a left join of this table with tbl_statuslog table. Thus I have a date irrespective of whether date exists in tbl_statuslog or not.

Then I want to backfill the isactive value for the date and user_id based on the previous value.

Can I use window function- example partition by user_id, order by date to achieve the result?

I'm blocked here because when evaluating the isactive value for a date and userid, how can I get access to prior 1 day value, or value of 2 days prior (and so on) when the previous day doesn't have value?

Solution

This answers the original version of the question.

I would suggest using recursion but only for each row:

with ts as (
      select ts.*,
             lead(date) over (partition by user_id order by date) as next_date
      from tbl_statuslog ts
     ),
     cte as (
      select user_id, date, isactive,
             coalesce(dateadd(day, -1, next_date), convert(date, getdate())) as end_date
      from ts
      union all
      select user_id, dateadd(day, 1, date), isactive, end_date
      from cte
      where date < end_date
     )
select user_id, date,  isactive
from cte;

Here is a db<>fiddle.