How to count business days from a given date in SQL Server using a calendar reference table

Question

I have two tables. One table is a Calendar that has a date column and a kindofday column. The kindofday column contains 'bankday', 'saturday', 'sunday' and 'holiday'. I need to account for holidays otherwise there are several queries on the net that would work...

        date                          kindofday
1999-08-24 00:00:00:000                bankday

I'm struggling to build a query that can count the number of working days from a given date. Something like:

Select [date] + 12 business days. I am not sure how to use the Calendar table as a reference for this - essentially I need to find a date in this calendar table then count down the column a number of business days and extract that date. I'll be putting this in a function so no worries about complexity. There is probably a stupid simple solution that I am just not seeing.

I'm open to any solutions that take holidays into consideration even if they do not use my calendar table. I can alter the calendar table however I need to in order to make this work... ie: using binary instead of string 'bankday' etc...

I have tried this: I have removed all the non bankdays from the calendar and added an integer key, and then set @date = (select key from calendar where @date=[date] and then added the amount of business days to that key and returned the date but the issue was when @date was a non bankday it wouldn't find a match in the calendar table...

Answer 1

Reword the question to "Of the next @count bankdays after @start_date, which one occurs last?"

SELECT @end_date = MAX(date)
FROM (
  SELECT TOP(@count)
    date
  FROM Calendar
  WHERE date > @start_date
    AND kindofday = 'bankday'
  ORDER BY date
) t