clear markdown compare help best fiddles feedback
clear markdown feedback
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;
3 rows affected
 hidden batch(es)


SELECT * FROM test;
id start_date end_date duration
1 2020-01-01 2020-01-03 2
2 2020-01-01 2020-01-08 5
3 2020-01-01 2020-01-14 9
 hidden batch(es)