Calculate Business days exclude weekend and holidays

Question

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'

Answer 1

One way
Declare @isi 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

Answer 2

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

Answer 3

This is best done with a "calendar table".

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