本文介绍了一种使用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--根据开始日期推出结束日期
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--计算工期
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
GOIF 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
扫一扫
1047
到【灌水乐园】发言
