sorting - How to get next row value based on previous row in SQL server?

Question

I have a table as follows,

here I want to re-generate the StartDate and EndDate data based on the NumberOfDaystoAdd values, and the StartDate for subsequent rows based on previous row's EndDate + 1day and in this sequence I need to exclude the weekend dates.

For Example, Here the 1st row values correct, second row startdate should be 2021-03-28 and EndDate should be 2021-03-10.

I want to apply the logic and select the data in same select query using sql server.

Expected result as follows,

---

Example data:

Answer 1

declare @t table
(
StartDate date,
EndDate date,
DaysToAdd int
);

insert into @t(StartDate, EndDate, DaysToAdd)
values('20210217', '20210227', 10),
('20210312', '20210310', 10), ('20210326', '20210401', 10), ('20210409', '20210401', 10),
('20210507', '20210401', 10), ('20210606', '20210529', 10),
('20210618', '20210417', 3), ('20210620', '20210309', 2),
('20300913', '20210227', 2), (null, '20300914', 4);


select *
from @t

select
dateadd(day, -DaysToAdd-1+count(*) over(order by isnull(StartDate, EndDate), EndDate) + sum(DaysToAdd) over(order by isnull(StartDate, EndDate), EndDate), min(StartDate) over()) as NewStartDate, 
dateadd(day, -1+count(*) over(order by isnull(StartDate, EndDate), EndDate) + sum(DaysToAdd) over(order by isnull(StartDate, EndDate), EndDate), min(StartDate) over()) as NewEndDate, 
* 
from @t;