SQL计算实际工作日(天)及两个时间(工作日)间隔(小时)!

 

去掉法定节假日(周六,周天)和指定节假日
USE [DBName]
GO
/****** 对象:  Table [dbo].[T_SYS_Holiday]    脚本日期: 11/08/2010 16:04:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_SYS_Holiday](
 
[id] [int] IDENTITY(1,1NOT NULL,
 
[name] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
 
[Hdate] [datetime] NOT NULL,
 
[isholiday] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
 
CONSTRAINT [PK_T_SYS_Holiday] PRIMARY KEY CLUSTERED 
(
 
[id] ASC
)
WITH (IGNORE_DUP_KEY = OFFON [PRIMARY]
ON [PRIMARY]

GO
SET ANSI_PADDING OFF

表记录示例:
1 国庆节 2010-10-1 0:00:00 节假日
2 国庆补班 2010-9-26 0:00:00 非节假日
3 国庆补班 2010-10-9 0:00:00 非节假日
4 国庆 2010-10-2 0:00:00 节假日
5 国庆 2010-10-3 0:00:00 节假日
6 国庆 2010-10-4 0:00:00 节假日
7 国庆 2010-10-5 0:00:00 节假日
8 国庆 2010-10-6 0:00:00 节假日
9 国庆 2010-10-7 0:00:00 节假日
10 中秋补班 2010-9-19 0:00:00 非节假日
11 中秋补班 2010-9-25 0:00:00 非节假日
12 中秋 2010-9-22 0:00:00 节假日
13 中秋 2010-9-23 0:00:00 节假日
14 中秋 2010-9-24 0:00:00 节假日


--计算实际工作日(单位:天)
create   FUNCTION   [dbo].[fn_WorkDay]
@dt_begin   datetime,     --计算的开始日期 
@dt_end     datetime         --计算的结束日期 
)RETURNS   int 
AS 
BEGIN 
DECLARE   @workday int,@bz   bit,@dt   datetime 
IF   @dt_begin> @dt_end 
  
SELECT   @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt 
ELSE 
 
SET   @bz=0
set  @workday=0 
 
WHILE   @dt_begin+1<=@dt_end 
  
BEGIN 
   
SELECT   @workday=CASE   
   
WHEN   ((((@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7   BETWEEN   1   AND   5 )or @dt_begin in(
    
select Hdate from t_sys_holiday where isholiday='非节假日'
    )) 
     
and (@dt_begin not in(select Hdate from t_sys_holiday where isholiday='节假日')))
   
THEN   @workday+1   ELSE   @workday   END
   
@dt_begin=@dt_begin+1 
  
END 
RETURN(CASE   WHEN   @bz=1   THEN   -@workday   ELSE   @workday   END
END 

 

--计算实际工作时间(单位:小时)
create   FUNCTION   [dbo].[fn_WorkHour]
@dt_begin   datetime,     --计算的开始日期 
@dt_end     datetime         --计算的结束日期 
)RETURNS   int 
AS 
BEGIN 
DECLARE   @workhour int,@workday int,@bz   bit,@dt   datetime ,@memorybegin_dt datetime
IF   @dt_begin> @dt_end 
  
SELECT   @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt 
ELSE 
 
SET   @bz=0
 
set  @workhour=0 
 
SET  @workday=0
 
SET @memorybegin_dt=@dt_begin
 
WHILE   dateadd(hh,0,dateadd(hh,1,@dt_begin))<=@dt_end 
  
BEGIN 
   
SELECT   @workhour=CASE   
   
WHEN   ((((@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7   BETWEEN   1   AND   5 )or @dt_begin in(
    
select Hdate from t_sys_holiday where isholiday='非节假日'
    ))  
and (@dt_begin not in(select Hdate from t_sys_holiday where isholiday='节假日')))
   
THEN   @workhour+1   ELSE   @workhour   END
   
set @dt_begin=dateadd(hh,0,dateadd(hh,1,@dt_begin))  
 
END 
 
set @workhour=@workhour--datediff(hh,@memorybegin_dt+@workday,@dt_end)
RETURN(CASE   WHEN   @bz=1   THEN   -@workhour   ELSE   @workhour   END
END 



select dbo.fn_WorkDay('2010-11-04 09:09:09','2010-11-05 09:09:09'--1
select dbo.fn_WorkDay('2010-11-04 09:09:09','2010-11-06 09:09:09'--2
select dbo.fn_WorkDay('2010-11-04 09:09:09','2010-11-07 09:09:09'--2
select dbo.fn_WorkDay('2010-11-04 09:09:09','2010-11-08 09:09:09'--2

select dbo.[fn_WorkHour]('2010-11-04 09:09:09','2010-11-05 09:09:09')--24
select dbo.[fn_WorkHour]('2010-11-04 09:09:09','2010-11-06 09:09:09')--39
select dbo.[fn_WorkHour]('2010-11-04 09:09:09','2010-11-07 09:09:09')--39
select dbo.[fn_WorkHour]('2010-11-04 09:09:09','2010-11-08 09:09:09')--48

 

 

posted @ 2010-11-08 16:16  Cutting.pht  阅读(1391)  评论(0编辑  收藏