question

ZaraZara-6283 avatar image
0 Votes"
ZaraZara-6283 asked EchoLiu-msft commented

Calculate Business days exclude weekend and holidays

Hi

How to get two business days from today(getdate()) exclude weekend and holidays?

Holiday table:
Create Table Holiday_Table( HDate date)
Insert INTO Holiday_Table(HDate)
values ('2021-12-31')
,('2021-12-28')
,('2021-12-27')
,('2021-12-24')
,('2021-11-26')
,('2021-11-25')
,('2021-09-06')

Result:


if getdate()='2021-09-07' then result should be '2021-09-02'
if getdate()='2021-11-29' then result should be '2021-11-23'
if getdate()='2021-11-30' then result should be '2021-11-24'
if getdate()='2021-12-29' then result should be '2021-12-22'

sql-server-transact-sql
· 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.


Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

One way
Declare @Days Table(TheDate date);

 With cte As
 (Select GetDate() As TheDate
 Union All
 Select DateAdd(day, -1, TheDate) As TheDate From cte
 Where DateDiff(day, TheDate, GetDate())< 30)
 Insert @Days(TheDate) Select TheDate From cte;
    
 Delete From d
 From @Days d
 Where d.TheDate In (Select h.HDate From Holiday_Table h)
   Or DateDiff(day, '19000101', d.TheDate) % 7 In (5,6); /* 5 is Saturday and 6 is Sunday */
    
 Select Min(TheDate) 
 From (Select Top 3 TheDate From @Days Order By TheDate Desc) a;

Tom

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.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please also try:

 DECLARE @workday INT
        ,@dt_begin DATE
        ,@today DATE
    
 SET @today =GETDATE()
 SET @workday =0 
 SET @dt_begin = DATEADD(d,-1,@today)
     
 WHILE @workday < 2
      BEGIN
         SELECT  @workday = CASE WHEN DATEPART(dw, @dt_begin) BETWEEN 1 AND 5
                 AND @dt_begin NOT IN(SELECT HDate FROM Holiday_Table)
                            THEN @workday + 1 ELSE @workday END 
         SET @dt_begin =DATEADD(d,-1,@dt_begin)
         IF @workday=2
         SELECT @today,DATEADD(d,1,@dt_begin)
      END

Regards,
Echo


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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

This is best done with a "calendar table".

See "udf_AddBusinessDays":
https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

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.