Wow.... five years...

Here is a quick example of a Calendar table. If you will search this site under the words "Calendar Table" you will find a number of articles with more in depth explanations.

create Table dbo.Calendar-- some people call this a date table

( CalendarDatedate

,Weekendbit

,Holidaybit

,EndOfMonthbit-- handy to simplify calculations involving end of month

)

GO

-- normally you'd insert a couple of year's worth of data into the table so you can look month's into the future if necessary

-- (use a tally table to increment getdate() to populate it quickly, then go back and do updates to set the bits)

-- but for our example purposes, we'll just put a few day's worth and pretend Tuesday is a holiday

insert into calendar

values ('2015-02-12',0,0,0),('2015-02-13',0,0,0),('2015-02-14',1,0,0),('2015-02-15',1,0,0), ('2015-02-16',0,0,0),('2015-02-17',0,1,0),('2015-02-18',0,0,0)

select * from Calendar

-- for this example, the definition of a workday is a day that is neither a weekend day, nor a holiday

-- so to find the number of workdays between Thursday the 12th and Wednesday the 18th (inclusive) simply run the following query.

-- although seven days are included in the BETWEEN range, three non-work days are omitted as either holidays or weekend days

select COUNT(*) as workdays

from Calendar

where Calendardate between '2/12/2015' and '2/18/2015'

and Weekend <> 1 and Holiday <> 1