calculate weekdays in sql for each stage and update 'Target Time' only if the fied is empty

Question

I have 4 columns in my table

ID, Stage Number, Start Time, Target End Time

For each stage the Target end time is different
For stage 1 - Target End Time is 2.5 days
For stage 2 - Target End Time is 8 days
For stage 3 - Target End Time is 5 days
For stage 4 - Target End Time is 7 days
For stage 5 - Target End Time is 0.5 days

Help me calculate the target end time only on the Weekdays in SQL

Say For stage 1 - 13/Nov is start date then the Target End Date will be 17/Nov

Thanks - Sarath

1. Can I get the time also printed. Example: 11/19/2020 7:45 AM.
2. Need to create stored procedure and I scheduled it running for every 10 mins.
   So It needs to check TargetEndTime field is null and I wanted to update this TargetEndTime only for the fields which has NULL.
   For few ID the TargetEndTime may be there so those ID should not get affected.

3.Also for stage 9 the duration is 0.5 days so I added - (9,0.5) will this calculate 0.5 days ?
It will have days like 0.5, 2.5,8.5 only (There is no 0.7 or other functions)

My old post - https://learn.microsoft.com/en-us/answers/questions/160506/calculate-weekdays-in-sql-for-each-stage.html

Answer 1

Hi @Sarath ,

Please refer below updated one:

Step 1 : create one function according to hours instead of days.

CREATE FUNCTION dbo.ufn_ADD_WORKING_DAYHOURS (  
    @DATETIME      DATETIME,  
    @NHOUR     INT)    
 RETURNS DATETIME       
  BEGIN           
         DECLARE @STARTDATE  INT  = 0  
         DECLARE @COUNT      INT  = 0  
         DECLARE @NEWDATE    DATETIME = DATEADD(HOUR, 12, @DATETIME)                                         
         WHILE @COUNT*12 < @NHOUR   
          BEGIN   
            IF DATEPART(WEEKDAY, @NEWDATE) NOT IN (7, 1)   
              SET @COUNT = @COUNT+1;  
              SELECT @NEWDATE = DATEADD(HOUR, 12, @NEWDATE), @STARTDATE += 12;  
          END   
          RETURN DATEADD(HOUR, @STARTDATE, @DATETIME);  
    END   
  GO  

Step 2: Call this function in the update statement.

DROP TABLE IF EXISTS MyTable  
       
  CREATE TABLE MyTable  (  
       ID int,   
       StageNumber int,   
       StartTime VARCHAR(100),  
       TargetEndTime VARCHAR(100)  
   );  
   INSERT INTO MyTable VALUES  
   (101, 1, '11/12/2020 07:45 AM',NULL),  
   (101, 2, '11/13/2020 07:45 AM',NULL),  
   (101, 3, '11/12/2020 07:45 AM','11/19/2020 07:45 AM'),  
   (101, 4, '11/13/2020 07:45 AM',NULL),  
   (101, 5, '11/12/2020 07:45 AM',NULL),  
   (102, 1, '11/16/2020 07:45 AM',NULL),  
   (102, 2, '11/17/2020 07:45 AM',NULL),  
   (102, 3, NULL,NULL),  
   (102, 4, NULL,NULL),  
   (102, 5, NULL,NULL);  
	            
   DECLARE @StageNumber TABLE (  
       StageNumber int,  
       NumberOfDays decimal(18,1)  
   );  
   INSERT INTO @StageNumber VALUES  
   (1, 2.5), (2, 8), (3, 5), (4, 7),(5, 0.5)  
    
  UPDATE A  
  SET TargetEndTime=FORMAT(dbo.ufn_ADD_WORKING_DAYHOURS( A.StartTime , B.NumberOfDays*24 ),'MM/dd/yyyy hh:mm tt')  
  FROM MyTable A  
   INNER JOIN @StageNumber B   
   ON A.StageNumber=B.StageNumber  
   WHERE TargetEndTime IS NULL AND StartTime IS NOT NULL  
      
  SELECT * FROM MyTable  

Output:

ID	StageNumber	StartTime	TargetEndTime  
101	1	11/12/2020 07:45 AM	11/16/2020 07:45 PM  
101	2	11/13/2020 07:45 AM	11/25/2020 07:45 AM  
101	3	11/12/2020 07:45 AM	11/19/2020 07:45 AM  
101	4	11/13/2020 07:45 AM	11/24/2020 07:45 AM  
101	5	11/12/2020 07:45 AM	11/12/2020 07:45 PM  
102	1	11/16/2020 07:45 AM	11/18/2020 07:45 PM  
102	2	11/17/2020 07:45 AM	11/27/2020 07:45 AM  
102	3	NULL	NULL  
102	4	NULL	NULL  
102	5	NULL	NULL  

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--How to convert Profiler trace into a SQL Server table