sql跳过非工作日（周末和节假日）

简介：场景1：基于开始日期和工期，推算结束日期。 场景2：基于开始日期和结束日期，计算工期 注：需要自己做界面维护工作日表(s_WorkDay)和节假日表(s_SpecialDay) 

涉及到的数据表

IF OBJECT_ID('s_WorkDay') IS NULL 
BEGIN 
     CREATE TABLE s_WorkDay([Monday] [TINYINT] 
          ,[Tuesday] [TINYINT] 
          ,[Wednesday] [TINYINT] 
          ,[Thursday] [TINYINT] 
          ,[Friday] [TINYINT] 
          ,[Saturday] [TINYINT] 
          ,[Sunday] [TINYINT] 
     )
END
GO

IF OBJECT_ID('s_SpecialDay') IS NULL 
BEGIN 
     CREATE TABLE s_SpecialDay([SpecialDayGUID] [UNIQUEIDENTIFIER] PRIMARY KEY CLUSTERED NOT NULL  
          ,[BeginDate] [DATETIME] 
          ,[EndDate] [DATETIME] 
          ,[IsWorkDay] [TINYINT] 
          ,[Remarks] [VARCHAR](200) 
     )
END
GO

场景1：根据开始日期和工期，计算结束日期

--根据开始日期推出结束日期

IF EXISTS ( SELECT  * FROM    dbo.sysobjects WHERE   id = OBJECT_ID(N'[dbo].[fn_GetEndDate]') AND xtype IN ( N'FN', N'IF', N'TF' ) )

    DROP FUNCTION [dbo].[fn_GetEndDate]

GO

CREATE   FUNCTION fn_GetEndDate ( @date DATETIME,@Duration INT )

RETURNS DATETIME

AS

    BEGIN

        DECLARE @Edate DATETIME

        DECLARE @IsAdd INT

        SET @Edate=@date

        SET @Duration=@Duration-1

         DECLARE @NoWorkDay TABLE(iDay int)

        --非工作日枚举

        INSERT INTO @NoWorkDay(iDay) SELECT * FROM dbo.fn_NoWorkDay()

       

        WHILE ( @Duration > 0 )

            BEGIN

                SET @IsAdd=0

                --默认往后+1

                SET @Edate=DATEADD(day, 1, @Edate)

                --如果非工作日，重复循环，否则跳下一步

                IF EXISTS( SELECT TOP 1 1 FROM s_workDay WHERE DATEPART(weekday, @Edate) NOT IN (SELECT * FROM @NoWorkDay))

                BEGIN

                 SET @IsAdd=1

                 SET @Duration = @Duration - 1

                END

                --如果当前日期在特殊非工作日中，则不跳

                IF EXISTS( SELECT TOP 1 1 FROM s_SpecialDay WHERE (@Edate BETWEEN begindate AND enddate) AND IsWorkDay=0 AND @IsAdd=1)

                BEGIN

                  SET @Duration = @Duration + 1

                END

                --如果当前日期在特殊工作日中，则跳1

                  IF EXISTS( SELECT TOP 1 1 FROM s_SpecialDay WHERE (@Edate BETWEEN begindate and enddate) AND IsWorkDay=1 AND @IsAdd=0)

                BEGIN

                  SET @Duration = @Duration -1

                END

            END

        RETURN @Edate

    END

GO

场景2：根据两个日期，计算工期

--计算工期

IF EXISTS ( SELECT  * FROM    dbo.sysobjects WHERE   id = OBJECT_ID(N'[dbo].[fn_GetDuration]') AND xtype IN ( N'FN', N'IF', N'TF' ) )

    DROP FUNCTION [dbo].[fn_GetDuration]

GO

CREATE   FUNCTION fn_GetDuration(@BeginDate DATETIME,@EndDate DATETIME)

RETURNS INT

AS

    BEGIN

        DECLARE @iCount INT

        --A:取出常规工作日

        SELECT  @iCount = ISNULL(dbo.fn_GetWorkDay(@BeginDate, @EndDate), 0) 

       

        --B:减去特殊非工作日

        SELECT  @iCount = @iCount

                - ISNULL(SUM(dbo.fn_GetWorkDay(CASE WHEN begindate < @BeginDate

                                                    THEN @BeginDate

                                                    ELSE begindate

                                               END,

                                               CASE WHEN enddate > @EndDate

                                                    THEN @EndDate

                                                    ELSE enddate

                                               END)), 0)

        FROM    s_SpecialDay

        WHERE   isworkday = 0

                AND SpecialDayGUID NOT IN (

                SELECT  SpecialDayGUID

                FROM    s_SpecialDay

                WHERE   ( begindate > @EndDate

                          AND enddate > @EndDate

                        )

                        OR ( begindate < @BeginDate

                             AND enddate < @BeginDate

                           ) )

     

        ----C:加上特殊工作日

        SELECT  @iCount = @iCount + ISNULL(SUM(DATEDIFF(dd,

                                                        CASE WHEN begindate < @BeginDate

                                                             THEN @BeginDate

                                                             ELSE begindate

                                                        END,

                                                        CASE WHEN enddate > @EndDate

                                                             THEN @EndDate

                                                             ELSE enddate

                                                        END)

                                               - dbo.fn_GetWorkDay(CASE

                                                              WHEN begindate < @BeginDate

                                                              THEN @BeginDate

                                                              ELSE begindate

                                                              END,

                                                              CASE

                                                              WHEN enddate > @EndDate

                                                              THEN @EndDate

                                                              ELSE enddate

                                                              END)), 0)

        FROM    s_SpecialDay

        WHERE   isworkday = 1

                AND SpecialDayGUID NOT IN (

                SELECT  SpecialDayGUID

                FROM    s_SpecialDay

                WHERE   ( begindate > @EndDate

                          AND enddate > @EndDate

                        )

                        OR ( begindate < @BeginDate

                             AND enddate < @BeginDate

                           ) )

        RETURN @iCount

    END

GO

需要用到的函数

IF EXISTS ( SELECT  *

            FROM    dbo.sysobjects

            WHERE   id = OBJECT_ID(N'[dbo].[fn_NoWorkDay]')

                    AND xtype IN ( N'FN', N'IF', N'TF' ) )

    DROP FUNCTION [dbo].[fn_NoWorkDay]

GO

--输出非工作日

CREATE   FUNCTION fn_NoWorkDay()

RETURNS @NoWorkDay TABLE ( iDay INT )

AS

    BEGIN

        INSERT  INTO @NoWorkDay

                ( iDay

                )

                SELECT  2

                FROM    s_WorkDay

                WHERE   Monday = 0

        INSERT  INTO @NoWorkDay

                ( iDay

                )

                SELECT  3

                FROM    s_WorkDay

                WHERE   Tuesday = 0

        INSERT  INTO @NoWorkDay

                ( iDay

                )

                SELECT  4

                FROM    s_WorkDay

                WHERE   Wednesday = 0

        INSERT  INTO @NoWorkDay

                ( iDay

                )

                SELECT  5

                FROM    s_WorkDay

                WHERE   Thursday = 0

        INSERT  INTO @NoWorkDay

                ( iDay

                )

                SELECT  6

                FROM    s_WorkDay

                WHERE   Friday = 0

        INSERT  INTO @NoWorkDay

                ( iDay

                )

                SELECT  7

                FROM    s_WorkDay

                WHERE   Saturday = 0

        INSERT  INTO @NoWorkDay

                ( iDay

                )

                SELECT  1

                FROM    s_WorkDay

                WHERE   Sunday = 0

        RETURN 

    END        

 

Go

--计算工作日

IF EXISTS ( SELECT  *

            FROM    dbo.sysobjects

            WHERE   id = OBJECT_ID(N'[dbo].[fn_GetWorkDay]')

                    AND xtype IN ( N'FN', N'IF', N'TF' ) )

    DROP FUNCTION [dbo].[fn_GetWorkDay]

GO

CREATE   FUNCTION fn_GetWorkDay(@BeginDate DATETIME,@EndDate DATETIME)

RETURNS INT

AS

    BEGIN

        DECLARE @NoWorkDay TABLE(iDay int)

        --非工作日枚举

        INSERT INTO @NoWorkDay(iDay) SELECT * FROM fn_NoWorkDay()

        DECLARE @i INT,@iCount INT

        SET @iCount=0

        SET @i = DATEDIFF(day, @BeginDate, @EndDate) 

        WHILE ( @i >= 0 )

            BEGIN

                --如果开始日期往后顺延，遇到非工作日，则不计数

                IF EXISTS( SELECT TOP 1 1 FROM s_workDay WHERE DATEPART(weekday, @BeginDate) NOT IN (SELECT * FROM @NoWorkDay))

                BEGIN

                 SET @iCount=@iCount+1

                END

                SET @BeginDate=DATEADD(day, 1, @BeginDate)

                SET @i = @i - 1 

            END

        RETURN @iCount

    END

go