By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601453 fiddles created (47969 in the last week).
CREATE TABLE test (id INT, start_date DATE, end_date DATE, duration INT);
INSERT INTO test VALUES
(1,'2020-01-01',NULL,2),
(2,'2020-01-01',NULL,5),
(3,'2020-01-01',NULL,9);
SELECT * FROM test;
id
start_date
end_date
duration
1
2020-01-01
2
2
2020-01-01
5
3
2020-01-01
9
…
hidden batch(es)
WITH cte AS ( SELECT id,
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) > 5
THEN 9 - DATEPART(WEEKDAY, start_date)
ELSE 1
END, start_date) cur_date, duration-1 reminder
FROM test
UNION ALL
SELECT id,
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, cur_date) > 5
THEN 3
ELSE 1
END, cur_date),
reminder - 1
FROM cte
WHERE reminder > 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
AND cte.reminder = 0;
