工作日计算,该怎么处理

--工作日处理函数(标准节假日)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--计算两个日期相差的工作天数CREATE FUNCTION f_WorkDay(@dt_begin datetime,  --计算的开始日期@dt_end  datetime    --计算的结束日期)RETURNS intASBEGIN    DECLARE @workday int,@i int,@bz bit,@dt datetime    IF @dt_begin>@dt_end        SELECT @bz=1,@[email protected]_begin,@[email protected]_end,@[email protected]    ELSE        SET @bz=0    SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,        @[email protected]/7*5,        @dt_begin=DATEADD(Day,@i/7*7,@dt_begin)    WHILE @dt_begin<[email protected]_end    BEGIN        SELECT @workday=CASE             WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5            THEN @workday+1 ELSE @workday END,            @[email protected]_begin+1    END    RETURN(CASE WHEN @bz=1 THEN [email protected] ELSE @workday END)ENDGO/*=================================================================*/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--在指定日期上,增加指定工作天数后的日期CREATE FUNCTION f_WorkDayADD(@date    datetime,  --基础日期@workday int       --要增加的工作日数)RETURNS datetimeASBEGIN    DECLARE @bz int    --增加整周的天数    SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END        ,@date=DATEADD(Week,@workday/5,@date)        ,@[email protected]%5    --增加不是整周的工作天数    WHILE @workday<>0         SELECT @date=DATEADD(Day,@bz,@date),            @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5                THEN @[email protected] ELSE @workday END    --避免处理后的日期停留在非工作日上    WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6)         SET @date=DATEADD(Day,@bz,@date)    RETURN(@date)END--工作日处理函数(自定义节假日)if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [tb_Holiday]GO--定义节假日表CREATE TABLE tb_Holiday(HDate smalldatetime primary key clustered, --节假日期Name nvarchar(50) not null)             --假日名称GOif 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--计算两个日期之间的工作天数CREATE FUNCTION f_WorkDay(@dt_begin datetime,  --计算的开始日期@dt_end  datetime   --计算的结束日期)RETURNS intASBEGIN    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)))ENDGOif 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--在指定日期上增加工作天数CREATE FUNCTION f_WorkDayADD(@date    datetime,  --基础日期@workday int       --要增加的工作日数)RETURNS datetimeASBEGIN    IF @workday>0        WHILE @workday>0            SELECT @[email protected][email protected],@workday=count(*)            FROM tb_Holiday            WHERE HDate BETWEEN @date AND @[email protected]    ELSE        WHILE @workday<0            SELECT @[email protected][email protected],@workday=-count(*)            FROM tb_Holiday            WHERE HDate BETWEEN @date AND @[email protected]    RETURN(@date)END