calculate weekdays in sql for each stage

Hi @Sarath ,

We could create one function as below firstly.

CREATE FUNCTION dbo.ufn_ADD_WORKING_DAYS (  
  @DATE      DATE,  
  @NDAYS     INT     
) RETURNS DATE       
BEGIN           
       IF @DATE IS NULL  
         BEGIN         
           SET @DATE = GETDATE();  
         END  
       DECLARE @STARTDATE  INT  = 0  
       DECLARE @COUNT      INT  = 0  
       DECLARE @NEWDATE    DATE = DATEADD(DAY, 1, @DATE)                                           
       WHILE @COUNT < @NDAYS   
        BEGIN   
          IF DATEPART(WEEKDAY, @NEWDATE) NOT IN (7, 1) --AND @NEWDATE NOT IN ( SELECT DT_HOLIDAY FROM TB_HOLIDAYS )   
            SET @COUNT += 1;  
            SELECT @NEWDATE = DATEADD(DAY, 1, @NEWDATE), @STARTDATE += 1;  
        END   
        RETURN DATEADD(DAY, @STARTDATE, @DATE);  
  END   
GO  

Then we could call this function as below:

CREATE TABLE MyTable  (  
     ID int,   
     StageNumber int,   
     StartTime date,  
	 TargetEndTime date  
 );  
 INSERT INTO MyTable VALUES  
 (101, 1, '2020-11-12',NULL),  
 (101, 2, '2020-11-13',NULL),  
 (101, 3, '2020-11-18',NULL),  
 (101, 4, '2020-11-19',NULL);  
      
 DECLARE @StageNumber TABLE (  
     StageNumber int,  
     NumberOfDays int  
 );  
 INSERT INTO @StageNumber VALUES  
 (1, 2), (2, 8), (3, 5), (4, 7);  
  
UPDATE A  
SET A.TargetEndTime=dbo.ufn_ADD_WORKING_DAYS( CAST(A.StartTime AS DATE), B.NumberOfDays )  
FROM MyTable A  
 INNER JOIN @StageNumber B   
 ON A.StageNumber=B.StageNumber  
  
SELECT * FROM MyTable  

Output:
ID StageNumber StartTime TargetEndTime
101 1 2020-11-12 2020-11-16
101 2 2020-11-13 2020-11-25
101 3 2020-11-18 2020-11-25
101 4 2020-11-19 2020-11-30

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?--- Hot issues November
Hot issues November--How to convert Profiler trace into a SQL Server table -- Hot issues November

You should use a calendar table. https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

Look at the proc dbo.udf_AddBusinessDays as an example of how you could count days.

Try this:

DECLARE @MyTable TABLE (  
	ID int,   
	StageNumber int,   
	StartTime date  
);  
INSERT INTO @MyTable VALUES  
(101, 1, '2020-11-12'),  
(101, 2, '2020-11-13'),  
(101, 3, '2020-11-18'),  
(101, 4, '2020-11-19');  
  
DECLARE @StageNumber TABLE (  
	StageNumber int,  
	NumberOfDays int  
);  
INSERT INTO @StageNumber VALUES  
(1, 2), (2, 8), (3, 5), (4, 7);  
  
SELECT  t.ID,   
		t.StageNumber,   
		t.StartTime,   
		CASE   
			WHEN DATEPART(weekday, t.StartTime) + s.NumberOfDays <= 6 THEN DATEADD(day, s.NumberOfDays, t.StartTime)   
			ELSE DATEADD(day, (s.NumberOfDays + (2*((DATEPART(weekday, t.StartTime) + s.NumberOfDays) / 6))), t.StartTime)  
		END AS TargetEndTime  
FROM @MyTable AS t  
INNER JOIN @StageNumber AS s ON s.StageNumber = t.StageNumber;  

Here is the output:

While you can technically answer your question by using the day of week, as others have suggested, your very next question is going to be "how do I exclude company holidays". For that, you need to use a calendar table.