CREATE FUNCTION addworkdays_jeff(@StartDate DATETIME, @DaysToAdd INT, @CountToday INT)
RETURNS DATETIME
     AS
  BEGIN --------------------------------------------------------------------------------------
 RETURN (
         SELECT d.EndDate 
              + CASE WHEN DATENAME(dw,d.EndDate) = 'Saturday' THEN 2
                     WHEN DATENAME(dw,d.EndDate) = 'Sunday'   THEN 1  
                     ELSE 0
                END AS EndDate 
           FROM (SELECT DATEADD(wk,@DaysToAdd/5, @StartDate
                                               + CASE WHEN DATENAME(dw,@StartDate) = 'Saturday' THEN 2 
                                                      WHEN DATENAME(dw,@StartDate) = 'Sunday'   THEN 1
                                                      ELSE 0 
                                                 END
                                               + @DaysToAdd%5-@CountToday
) AS EndDate
)d
) --End of Return
    END --End of Function
go

CREATE function fn_AddBizDays
--Accepts Date ‘X’ and Integer ‘Y’

(
@dtBeginDate datetime,
@iBizDays int
)

RETURNS datetime
--Result is Date ‘Z’ that is ‘Y’ business days after supplied Date ‘X’

AS

BEGIN


DECLARE @dtEndDate datetime
DECLARE @dtDateHolder datetime
DECLARE @dtNextHol datetime

SET @dtDateHolder = @dtBeginDate
SET @dtEndDate = DATEADD(d,@iBizDays,@dtBeginDate)
--Find the first Public Holiday Date
select @dtNextHol = MIN(holiday_date) FROM tblHolidays where holiday_date >=@dtDateHolder

WHILE (@dtDateHolder <= @dtEndDate)
BEGIN

--Is the date being checked a Saturday or Sunday?
IF((DATEPART(dw, @dtDateHolder) in (1, 7))
--Is the date being checked a holiday?
OR (DATEADD(dd, 0, DATEDIFF(dd, 0, @dtDateHolder))=@dtNextPH))
--NOTE: DATEDIFF trick used above to discard TIMESTAMP

BEGIN

-- Extend the date range for Weekends and Holidays by one day
SET @dtEndDate = DATEADD(d,1,@dtEndDate)
if (DATEADD(dd, 0, DATEDIFF(dd, 0, @dtDateHolder))=@dtNextHol)
BEGIN
-- get the next  public holiday date
select @dtNextHol = MIN(holiday_date ) FROM tblHolidays where holiday_date >@dtDateHolder
END
END

--Move to the next day in the range and loop back to check it
SET @dtDateHolder = DATEADD(d,1,@dtDateHolder)

END

--Respond with newly determined end date
RETURN @dtEndDate

END