How to calculate the workdays in a period?

Execute the following script in Query Editor to create a Holiday table and a scalar function for workdays calculation and test it.

USE AdventureWorks
GO
CREATE TABLE dbo.Holiday (
CountryCode char(2) NOT NULL,
[HolidayDate] [smalldatetime] NOT NULL ,
CONSTRAINT [PK_holidayDate] PRIMARY KEY CLUSTERED
(
[CountryCode], [HolidayDate]
)
)
GO
INSERT dbo.Holiday values ('US', '2008-01-01')
INSERT dbo.Holiday values ('US', '2008-02-18')
INSERT dbo.Holiday values ('US', '2008-05-26')
INSERT dbo.Holiday values ('US', '2008-07-04')
INSERT dbo.Holiday values ('US', '2008-09-01')
INSERT dbo.Holiday values ('US', '2008-10-13')
INSERT dbo.Holiday values ('US', '2008-11-27')
INSERT dbo.Holiday values ('US', '2008-12-25')
GO


CREATE FUNCTION fnWorkDays ( @StartDate datetime, @EndDate datetime)
RETURNS INT
AS
BEGIN
RETURN(SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- COUNT(*) FROM dbo.Holiday where HolidayDate between @StartDate and @EndDate)
END
GO

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = getdate() - 200
SET @EndDate = getdate() - 100
SELECT WorkDays = dbo.fnWorkDays ( @StartDate, @EndDate)
GO
DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = getdate() + 100
SET @EndDate = getdate() + 400
SELECT WorkDays = dbo.fnWorkDays ( @StartDate, @EndDate)
GO