SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @t table
(
    id int,
    nbr int,
    dt date,
    status smallint
);

insert into @t (id, nbr, dt, status)
values
(1,9,'2023-11-1',0),
(1,9,'2023-12-1',1),
(1,9,'2024-1-1',0),
(1,2,'2023-10-1',1),
(1,2,'2023-11-2',0),
(1,2,'2023-12-13',0),
(1,3,'2023-10-1',0),
(1,3,'2023-11-2',0),
(1,3,'2023-12-13',1);
  
with cte as (
    select id, nbr, dt, status
        -- get a row number so we can only show the calculated data once per partition
        , row_number() over (partition by id, nbr order by id, nbr, dt asc, status desc) rn
        -- Determine whether status 1 exists within the partition
        , max(status) over (partition by id, nbr) partition_status
        -- Get the first date where Status = 1
        , min(case when Status = 1 then dt else null end) over (partition by id, nbr) partition_dt1
        -- Get the last date when Status =1 
        , max(case when Status = 1 then dt else null end) over (partition by id, nbr) partition_dt2
    from @t
)
select id, nbr, dt, status
    -- On the first row of each partition show whether a status = 1 exists
    , case when rn = 1 then partition_status else 0 end Cont1Sta1
    -- On the first row of each partition show the date lag in days
    , case when rn = 1 then datediff(day, dt, coalesce(partition_dt1, dt)) else 0 end DateLagInDays
    -- On the first row of each partition show whether any records exist after the last Status = 1
    , case when rn = 1 and exists (select 1 from cte c2 where c2.id = c1.id and c2.nbr = c1.nbr and c2.dt > c1.partition_dt2) then 1 else 0 end Recurrence
from cte c1
order by id, nbr, dt asc, status desc;

id	nbr	dt	status	Cont1Sta1	DateLagInDays	Recurrence
1	2	2023-10-01	1	1	0	1
1	2	2023-11-02	0	0	0	0
1	2	2023-12-13	0	0	0	0
1	3	2023-10-01	0	1	73	0
1	3	2023-11-02	0	0	0	0
1	3	2023-12-13	1	0	0	0
1	9	2023-11-01	0	1	30	1
1	9	2023-12-01	1	0	0	0
1	9	2024-01-01	0	0	0	0

Warning: Null value is eliminated by an aggregate or other SET operation.