Add a calendar table with a working_day field type int =1 on working days, and 0 on non-working days.

i.e.

CREATE TABLE dbo.calendar(

calendar_date date NOT NULL PRIMARY KEY,

working_day INT NOT NULL DEFAULT 1)

Holidays, and weekends, and emergency closed days get a 0 for the working_day

If you need to count partial working days, replace the INT with the appropriate numeric data type to handle the most granular fraction of a day you need to deal with.

then get the business to determine if you count the days between, include the starting day, and/or include the ending day.

then it comes down to the following for days between

SELECT

working_days = sum(working_day)

FROM

calendar

where

calendar_date > @start_date and calendar_date < @end_date

OR for days between + end day

SELECT

working_days = sum(working_day)

FROM

calendar

where

calendar_date > @start_date and calendar_date <= @end_date

OR for days between + start day

SELECT

working_days = sum(working_day)

FROM

calendar

where

calendar_date >= @start_date and calendar_date < @end_date

OR for days between + both start and end

SELECT

working_days = sum(working_day)

FROM

calendar

where

calendar_date >= @start_date and calendar_date <= @end_date

Obviously you need to join to the calendar table, but this will make things easy for you.