Calculate due date and exclude working/business days SQL

Question

I would like to calculate a due date. If the start date is 30/12/2020, I need to count 20 working days from start date and display the date for the 20th working date from start date.

For example if the start date is 30/12/2020 must give me 28/01/2021 (excludes saturdays and sundays and finds the 20th working day from 30/12/2020).

But I am unable to exclude the weekends.

  SELECT 
  DATEADD(DAY,20,CAST(CAST('2020-12-30' AS DATE) AS DATETIME))
  -(CASE WHEN DATENAME(dw,'2020-12-30') = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw,'2020-12-30') = 'Saturday' THEN 1 ELSE 0 END) AS DueDate 

thanks

Answer 1

Your best pick would be to build a calendar table, with a boolean flag that indicates whether each day is a working day or not. Then you would just do something like:

select dt
from calendar
where dt >= '20201231' and is_working_day = 1
order by dt
offset 19 rows fetch next 1 row only

The way your question is asked, however, one option enumerates the days in a subquery until the count of working days is reached:

declare @dt date = '20201231';
declare @no_days int = 20;

with cte as (
    select @dt as dt, @no_days as no_days
    union all
    select dateadd(day, 1, dt), 
        case when datename(weekday, dt) in ('Saturday', 'Sunday') 
            then no_days
            else no_days - 1
        end
    from cte
    where no_days > 1
)
select max(dt) as res from cte