10.工作日处理函数（自定义节假日）

叁歲伎倆 2024-02-19 17:44 34阅读 0赞

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)             --假日名称
    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
    
    --计算两个日期之间的工作天数
    CREATE FUNCTION f_WorkDay(
    @dt_begin datetime,  --计算的开始日期
    @dt_end  datetime   --计算的结束日期
    )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
    
    --在指定日期上增加工作天数
    CREATE FUNCTION f_WorkDayADD(
    @date    datetime,  --基础日期
    @workday int       --要增加的工作日数
    )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