sql计算工作日函数

最新推荐文章于 2024-05-21 14:23:18 发布

原创最新推荐文章于 2024-05-21 14:23:18 发布 · 1.7k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#工作 #sql #null #function #table #算法

这个算法还不够严谨，没有考虑到周六、日调换假期等细节的情况。

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