这个算法还不够严谨,没有考虑到周六、日调换假期等细节的情况。
1、建节假日表
CREATE TABLE [Holiday] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[BeginDate] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL ,
[AddUser] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[AddTime] [datetime] NOT NULL ,
CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
2、计算工作日函数
CREATE function [dbo].[WorkDay]
(
@beginday datetime,
@endday datetime
)
returns int
AS
begin
--set datefirst 1
declare @caldays int
declare @id int
select @caldays=0
while DATEDIFF(d, @beginday,@endday)>0
begin
select @beginday=dateadd(day,1,@beginday)
if datepart(dw,@beginday)>1 and datepart(dw,@beginday)<7
begin
SELECT @id=count(*) from holiday where @beginday between begindate and DATEADD(s,-1,DATEADD(day,1,enddate))
if(@id=0)
select @caldays=@caldays+1
end
end
return @caldays
end
3、推算指定日期前N个工作日的日期函数
CREATE function [dbo].[BeginDate]
(
@workday int ,
@endday datetime
)
returns datetime
AS
begin
declare @beginday datetime
declare @id int
select @beginday=@endday
while DATEDIFF(d, @beginday,@endday)<@workday
begin
select @beginday=dateadd(day,-1,@beginday)
if datepart(dw,@beginday)>1 and datepart(dw,@beginday)<7
begin
SELECT @id=count(*) from holiday where @beginday between begindate and DATEADD(s,-1,DATEADD(day,1,enddate))
if(@id>0)
select @workday=@workday+1
end
else
begin
select @workday=@workday+1
end
end
if(datepart(dw,@beginday)=2)
begin
select @beginday=dateadd(day,-2,@beginday)
end
return @beginday
end