question

Sarath-2823 avatar image
0 Votes"
Sarath-2823 asked Sarath-2823 commented

calculate weekdays in sql for each stage

Hi Team,

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 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

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

39289-image.png

Thanks - Sarath


sql-server-generalsql-server-transact-sql
image.png (5.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Sarath-2823,

Could you please validate all the answers so far and provide any update?


Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered Sarath-2823 commented

Hi @Sarath-2823,

can I get the time also printed along with this. Example: 11/19/2020 7:45 AM.

have created the above code in a 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.

Please refer below method:

Create a few function as below:

 CREATE FUNCTION dbo.ufn_ADD_WORKING_DAYTIMES (
    @DATETIME      DATETIME,
    @NDAYS     INT   
  ) RETURNS DATETIME     
  BEGIN         
         IF @DATETIME IS NULL
           BEGIN       
             SET @DATETIME = GETDATE();
           END
         DECLARE @STARTDATE  INT  = 0
         DECLARE @COUNT      INT  = 0
         DECLARE @NEWDATE    DATE = DATEADD(DAY, 1, @DATETIME)                                         
         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, @DATETIME);
    END 
  GO

Call this function as below:

  DROP TABLE IF EXISTS MyTable
     
  CREATE TABLE MyTable  (
       ID int, 
       StageNumber int, 
       StartTime DATETIME,
       TargetEndTime VARCHAR(100)
   );
   INSERT INTO MyTable VALUES
   (101, 1, '2020-11-12 09:30:00',NULL),
   (101, 2, '2020-11-13 15:23:00',NULL),
   (101, 3, '2020-11-18 07:45:00','11/19/2020 07:45 PM'),
   (101, 4, '2020-11-19 10:45:00',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=FORMAT(dbo.ufn_ADD_WORKING_DAYTIMES( A.StartTime , B.NumberOfDays ),'MM/dd/yyyy hh:mm tt')
  FROM MyTable A
   INNER JOIN @StageNumber B 
   ON A.StageNumber=B.StageNumber
     WHERE TargetEndTime IS  NULL
    
  SELECT * FROM MyTable

Output:

 ID StageNumber StartTime TargetEndTime
 101 1 2020-11-12 09:30:00.000 11/16/2020 09:30 AM
 101 2 2020-11-13 15:23:00.000 11/25/2020 03:23 PM
 101 3 2020-11-18 07:45:00.000 11/19/2020 07:45 PM
 101 4 2020-11-19 10:45:00.000 11/30/2020 10:45 AM

3.Also for stage 9 the duration is 0.5 days so I added - (9,0.5) will this calculate 0.5 days ?

Right now both functions are not suitable for 0.5 day.

Please provide more details about this one like desired output of '2020-11-13 15:23:00.000' adding 0.5 days.

Is there any other conditions? Like 0.7 day or only 0.5 day?

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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:

39513-image.png



image.png (4.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered Sarath-2823 commented

Hi @Sarath-2823,

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Melissa this helped me .

I have 3 more new queries related to same :

  1. can I get the time also printed along with this. Example: 11/19/2020 7:45 AM.

  2. have created the above code in a 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 ?

Regards - Sarath

0 Votes 0 ·

Hi @Sarath-2823,

Actually you provided the sample data as date instead of datetime in your original post.

If you would like to print datetime or include the 0.5 day condition, my code will not be suitable.

We recommend you to post another question with new sample data (datetime and 0.5 day conditions) and desired output since we already found out solutions to your original question.

You could use below query to update the function or final update statement.

 select FORMAT(getdate(),'MM/dd/yyyy hh:mm tt')
    
 select FORMAT(TargetEndTime,'MM/dd/yyyy hh:mm tt') from MyTable

Best regards
Melissa



0 Votes 0 ·

Hi Melissa,

The code was accepted as it was working fine for the year 2020.
After new year 2021 the code is not working well.
it just add days to the given date.
It is not calculating the weekends.
Please assist asgain.

Thanks-Sarath

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.