How to calculate end_date from start_date and duration in SQL Server Query I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. 

I am providing the start_date and duration as the input to calculate end-date. 

While calculating the end_date the Saturday and Sundays are not considered.

Also if the actual_date is having value that value is saved as end_date.

Example : Start_date = 18/05/2020 , Duration : 10. So the End_date should be calculated by excluding 23/05/2020 and 24/05/2020. 

I had tried different ways to archive this.

The below is the alter query for the column end_date.

This is meeting half of my requirement but dont know how to exclude the weekends (Saturday and Sunday) from the start_date and duration.

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as CAST(
CASE 
WHEN (Actual_Date='' or Actual_Date IS NULL) then  DATEADD(day, Duration, Start_Date)
ELSE Actual_Date
END as DATE
)
GO



Please help me on this.
Iterative solution:

WITH cte AS ( SELECT id, 
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) &gt; 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) &gt; 5 
                                       THEN 3 
                                       ELSE 1 
                                       END, cur_date), 
                     reminder - 1
              FROM cte
              WHERE reminder &gt; 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
  AND cte.reminder = 0;


fiddle

PD. It is optimal hardly... for large duration values you may int. divide it by 7 and add according weeks (5 days per each) then iterate by int. reminder only.