A different Weekdays Between test

-- By Brendt Hess
-- Inspired by work at www.experts-exchange.com, and by the script by Rick Carisse
-- This calculates the correct # of weekdays (defined as Monday through Friday
--   no matter what value is set for the first day of the week.

Create Function [dbo].[ufn_WeekdaysBetween]
( @StartDate datetime,
  @EndDate Datetime)
RETURNS int
AS
BEGIN
-- Since I need to make an adjustment based on if the Start is on Sunday
-- let's save those values into variables

Declare @D1Day int
Declare @D2Day int

Set @D1Day = Datepart(dw, @StartDate)
Set @D2Day = Datepart(dw, @EndDate)

-- Since day of deek can vary depending on the Datefirst value, we need to calculate
-- the actual Day of Week values for Sunday and Saturday.
--
-- To simplify, we use a constant date known to be a Sunday

Declare @dSunday int
Declare @dSaturday int

Set @dSaturday = datepart(dw, Convert(smalldatetime, '2000-01-01'))
Set @dSunday = @dSaturday + 1
If @dSunday > 7
Set @dSunday = 1

-- Now that we have these values, we are ready to calculate the number of days.
-- Basic formula is:  # of days apart the dates are Plus 1 minus (2 * # of weeks apart)
-- If the start date is on Sunday, subtract one day.
--If the end date is on Saturday, subtract one day.
--The weekend adjustment takes care of all other cases.

Declare @Days int

Set @Days = DateDiff(d, @StartDate, @EndDate) 
- DateDiff(ww, @StartDate, @EndDate) * 2 + 1 
- CASE WHEN @D1Day = @dSunday Then 1 Else 0 END 
- CASE WHEN @d2Day = @dSaturday Then 1 Else 0 END 

Return @Days
END