If exists (select * from dbo. sysobjects where id = object_id (n' [tb_Holiday] ') and OBJECTPROPERTY (id, n'isusertable') = 1)
Drop table [tb_Holiday]
GO
-- Define holiday table
Create table tb_Holiday (
HDate smalldatetime primary key clustered, -- holiday period
Name nvarchar (50) not null) -- holiday Name
GO
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [f_WorkDay] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [f_WorkDay]
GO
-- Calculate the number of working days between two dates
Create function f_WorkDay
(@ Dt_begin datetime, -- start date of Calculation
@ Dt_end datetime -- end date of the calculation)
RETURNS int
AS
BEGIN
IF @ dt_begin> @ dt_end
RETURN (DATEDIFF (Day, @ dt_begin, @ dt_end) + 1-(select count (*) FROM tb_Holiday WHERE HDate BETWEEN @ dt_begin AND @ dt_end ))
RETURN (-(DATEDIFF (Day, @ dt_end, @ dt_begin) + 1-(select count (*) FROM tb_Holiday WHERE HDate BETWEEN @ dt_end AND @ dt_begin )))
END
GO
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [f_WorkDayADD] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [f_WorkDayADD]
GO
-- Add working days on the specified date
Create function f_WorkDayADD (
@ Date datetime, -- base date
@ Workday int -- the number of working days to be added)
RETURNS datetime
AS
BEGIN
IF @ workday> 0
WHILE @ workday> 0
SELECT @ date = @ date + @ workday, @ workday = count (*) FROM tb_Holiday WHERE HDate BETWEEN @ date AND @ date + @ workday
Else while @ workday <0
SELECT @ date = @ date + @ workday, @ workday =-count (*) FROM tb_Holiday WHERE HDate BETWEEN @ date AND @ date + @ workday
RETURN (@ date)
END