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
