简介:场景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