Set deadlines by adding business days or hours

/*
Includes 2 SP's, 2 UDF's and simple table for holidays. Part of a business calendar service (hence the bcs prefixes).
1. bcssp_AddBusinessDays -- pass datetime and days to add, retuns new datetime. Checks every day in range, so might have performance issues if passing very large number of days (adding 150,000 days took 4 minutes - I wish all my deadlines were that far out).
2. bcssp_AddBusinessHours -- pass datetime, hours to add, beginning and ending business hours as strings (e.g. '08:00:00','17:00:00'), returns new datetime
3. bcsfn_IsHoliday -- checks date to see if it exists in holiday table
4. bcsfn_IsWeekend -- checks date to see if on a weekend

Email any comments, suggestions, etc. to jdeupree@ltca.org 
  
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bcsfn_IsHoliday]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[bcsfn_IsHoliday]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bcsfn_IsWeekend]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[bcsfn_IsWeekend]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bcssp_AddBusinessDays]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[bcssp_AddBusinessDays]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bcssp_AddBusinessHours]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[bcssp_AddBusinessHours]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[holiday]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION [dbo].[bcsfn_IsHoliday] 
(@dDate datetime)
RETURNS int
AS  
BEGIN 
declare @iTrue int
select @iTrue = count(*) from holiday
where convert(char(10),hol_date,101) = convert(char(10),@dDate,101)
RETURN @iTrue
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION [dbo].[bcsfn_IsWeekend] 
(@dDate datetime)
RETURNS int
AS  
BEGIN 
declare @iTrue int
select @iTrue = case datepart(dw,@dDate)
when 1 then 1
when 7 then 1
else 0
end
RETURN @iTrue
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TABLE [dbo].[holiday] (
[hol_id] [int] IDENTITY (1, 1) NOT NULL ,
[hol_date] [datetime] NOT NULL ,
[hol_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*
procedure bcssp_AddBusinessDays
takes parameters@dBegDate datetime
@iDays int
returns@dEndDate datetime

returns a date that is @iDays business days from begin date @dBegDate
01/22/2003 jd
*/
CREATE PROCEDURE [dbo].[bcssp_AddBusinessDays]
@dBegDate datetime, @iDays int, @dEndDate datetime output
 AS

-- temporary holder for input date
declare @dDate datetime-- input date 
set @dDate = @dBegDate

-- temporary variables
declare @iDayCounter int-- day counter
set @iDayCounter = 1
declare @iWeFlag int-- weekend day flag
declare @iHolFlag int-- holiday flag
declare @iDaysAddedFlag int-- 'did we add any days in this loop?' flag

/*
-- use this code if at a later date we decide to 'massage' the times based on business hours
-- will probably need to pass business hours as params if this is used
-- if time > 5:00 PM then add 1 day and change time to 8:00 AM
if (datepart(hh,@dBegDate) > 16 and datepart(mm,@dBegDate) > 0)
begin
set @dDate = cast(convert(char(10),@dDate,101) + ' 08:00:00' as datetime)
set @iDays = @iDays + 1
end
-- if time < 8:00 AM then change time to 8:00 AM
if (datepart(hh,@dBegDate) < 8)
begin
set @dDate = cast(convert(char(10),@dDate,101) + ' 08:00:00' as datetime)
end

*/
while (@iDaysAddedFlag = 1 or @iDayCounter <= @iDays)
begin
set @dDate = dateadd(dd,1,@dDate)-- increment date by 1 day
set @iWeFlag = dbo.bcsfn_isweekend(@dDate)-- check to see if new date
-- is a weekend day
set @iHolFlag = dbo.bcsfn_isholiday(@dDate)-- check to see if holiday
if @iWeFlag + @iHolFlag = 0-- if neither
begin
set @iDayCounter = @iDayCounter + 1-- increment day counter
set @iDaysAddedFlag = 0-- set days added flag to 'no'
end
else
set @iDaysAddedFlag = 1-- set days added flag to 'yes'
end

set @dEndDate = @dDate

return
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*
procedure bcssp_AddBusinessHours
takes parameters@dBegDate datetime -- start datetime
@iHours int  -- hours to add
@cBusBeg char in form of time portion of datetime (e.g. '08:30:00') -- beginning time of business day
@cBusEnd char as above -- end time of business day
returns@dEndDate datetime

returns a date that is @iHours business hours from begin date @dBegDate
adds business days if neccesssary using bcssp_AddBusinessDays

input beginning and ending times for business day as strings
will cast to '01/01/1900 hh:mm:ss'
01/24/2003 jd
*/
CREATE PROCEDURE [dbo].[bcssp_AddBusinessHours] 
@dBegDate datetime, @iHours float, @cBusBeg char(8), @cBusEnd char(8), @dEndDate datetime output
AS

-- set time only portion of input date (convert to 01/01/1900)
declare @dBegTime datetime
set @dBegTime = cast(convert(char(8),@dBegDate,8) as datetime)

-- temp variables
declare @dDate datetime, @iTotalMinutes int
set @dDate = @dBegDate
set @iTotalMinutes = @iHours * 60

-- setup business beginning and ending times and
-- number of hours in a business day
declare @dBusBeg datetime, @dBusEnd datetime, @iBusMinutes int
set @dBusBeg = cast(@cBusBeg as datetime)-- '01/01/1900 hh:mm:ss'
set @dBusEnd = cast(@cBusEnd as datetime)-- '01/01/1900 hh:mm:ss'
set @iBusMinutes = datediff(mi,@dBusBeg,@dBusEnd)

-- figure if there are days to add 
declare @iDays int, @iMinutes int
set @iDays = @iTotalMinutes/@iBusMinutes-- input minutes > minutes per business day
set @iMinutes = @iTotalMinutes % @iBusMinutes-- leftover minutes

-- handle 3 types of begin times
-- Beginning time before businees day begins
if @dBegTime < @dBusBeg
begin
set @dBegTime = @dBusBeg -- set begin time to begin of business day
end

-- beginning time after business day ends
if @dBegTime > @dBusEnd
begin
set @iDays = @iDays + 1-- add a day
set @dBegTime = @dBusBeg-- set begin time to begin of business day
end

-- beginning time within business day
if @dBegTime between @dBusBeg and @dBusEnd
begin
-- if new end time will be > ending business time add 1 day and 
-- set the beginning time to the start of the business day
if dateadd(mi,@iMinutes,@dBegTime)> @dBusEnd
begin
set @iDays = @iDays + 1-- add a day
-- subtract # of minutes left in business day from leftover minutes
set @iMinutes = @iMinutes - datediff(mi,@dBegTime,@dBusEnd)
-- reset time portion of input date to beginning of business day
set @dBegTime = @dBusBeg 
end
end

-- if there are days to add, let the AddBusinessDays sp do it
if @iDays > 0
begin
exec bcssp_AddBusinessDays @dBegDate, @iDays, @dDate output
end

-- now we have the date, let's get the time
set @dDate = cast(convert(char(10),@dDate,101)+ ' ' + 
convert(char(10),dateadd(mi,@iMinutes,@dBegTime),8) as datetime)

set @dEndDate = @dDate

return
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO