跪求~~ SQL_计算有效工作时间

*小弟版本SQL server 2000


望大虾给出一个存储过程（或其他形式）

可计算出一段时间内的有效工作时间

参考数据如下：

上午有效工作时间--09:00:00  到  12:00:00
下午有效工作时间--13:30:00  到  18:00:00

并且除去周六周日

（最好可以把节假日也除去）

每步求注释  ---可讲解下

15 个解决方案

#1

--工作日处理函数(标准节假日)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO

--计算两个日期相差的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime,  --计算的开始日期
@dt_end  datetime    --计算的结束日期
)RETURNS int
AS
BEGIN
    DECLARE @workday int,@i 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
    SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
        @workday=@i/7*5,
        @dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
    WHILE @dt_begin<=@dt_end
    BEGIN
        SELECT @workday=CASE 
            WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
            THEN @workday+1 ELSE @workday END,
            @dt_begin=@dt_begin+1
    END
    RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO



/*=================================================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO

--在指定日期上,增加指定工作天数后的日期
CREATE FUNCTION f_WorkDayADD(
@date    datetime,  --基础日期
@workday int       --要增加的工作日数
)RETURNS datetime
AS
BEGIN
    DECLARE @bz int
    --增加整周的天数
    SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
        ,@date=DATEADD(Week,@workday/5,@date)
        ,@workday=@workday%5
    --增加不是整周的工作天数
    WHILE @workday<>0 
        SELECT @date=DATEADD(Day,@bz,@date),
            @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
                THEN @workday-@bz ELSE @workday END
    --避免处理后的日期停留在非工作日上
    WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6) 
        SET @date=DATEADD(Day,@bz,@date)
    RETURN(@date)
END





--工作日处理函数(自定义节假日)

if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_Holiday]
GO

--定义节假日表
CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered, --节假日期
Name nvarchar(50) not null)             --假日名称
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO

--计算两个日期之间的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime,  --计算的开始日期
@dt_end  datetime   --计算的结束日期
)RETURNS int
AS
BEGIN
    IF @dt_begin>@dt_end
        RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
            +1-(
                SELECT COUNT(*) FROM tb_Holiday
                WHERE HDate BETWEEN @dt_begin AND @dt_end))
    RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
        +1-(
            SELECT COUNT(*) FROM tb_Holiday
            WHERE HDate BETWEEN @dt_end AND @dt_begin)))
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO

--在指定日期上增加工作天数
CREATE FUNCTION f_WorkDayADD(
@date    datetime,  --基础日期
@workday int       --要增加的工作日数
)RETURNS datetime
AS
BEGIN
    IF @workday>0
        WHILE @workday>0
            SELECT @date=@date+@workday,@workday=count(*)
            FROM tb_Holiday
            WHERE HDate BETWEEN @date AND @date+@workday
    ELSE
        WHILE @workday<0
            SELECT @date=@date+@workday,@workday=-count(*)
            FROM tb_Holiday
            WHERE HDate BETWEEN @date AND @date+@workday
    RETURN(@date)
END

#2

引用 1 楼 ssp2009 的回复:

SQL code
--工作日处理函数(标准节假日)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO

--计算两个日期相差的工作天数
C……

#3

回复#1

那样可以把一天中不工作的时间排除么 ？

我用存储过程计算的一天中有效工作时间如下

declare 
@a datetime,
@b datetime
set
@a='2011-08-01 09:00:00'
set
@b='2011-08-01 18:00:00'

select 
case 
when @a>='2011-08-01 09:00:00' and @b<='2011-08-01 13:30:00' then datediff(mi,@a,'2011-08-01 12:00:00')
when @a>='2011-08-01 12:00:00' and @b<='2011-08-01 18:00:00' then datediff(mi,'2011-08-01 13:30:00',@b)
when @a>='2011-08-01 09:00:00' and @b<='2011-08-01 18:00:00' then datediff(mi,@a,'2011-08-01 12:00:00')+datediff(mi,'2011-08-01 13:30:00',@b)
when @a>='2011-08-01 12:00:00' and @b<='2011-08-01 13:30:00' then 0

end 

#4

declare @d1 datetime;
declare @d2 datetime;
select @d1='2011-09-01',@d2='2011-09-20';

select DATEADD(DD,a.number,@d1) as 日期,DATEPART(dw,DATEADD(DD,a.number,@d1))-1 as 星期 from master..spt_values a where type='p' and number<=DATEDIFF(dd,@d1,@d2)
where DATEPART(dw,DATEADD(DD,a.number,@d1))-1 not in(6,0)
-- 2000只要把 master..spt_values 变成一个自己创建的畏助表就可以了

#5

回#4
《master..spt_values 变成一个自己创建的畏助表就可以了》


没明白。

#6

引用 5 楼 xiaowei_sql 的回复:

回#4
《master..spt_values 变成一个自己创建的畏助表就可以了》


没明白。

辅助表
就是你自定义一个表，这个表就一个字段就行，值是1到1000或者1到10000或者1到100000 你自己定

#7

构造时间表 再计算

#8

declare @sdate datetime 
declare @edate datetime 
set @sdate = '2009-8-30' 
set @edate = '2009-9-5' 


select 
    dateadd(dd,num,@sdate) 
from 
    (select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t) a 
where 
    dateadd(dd,num,@sdate) <=@edate 

/* 
                                                      
------------------------------------------------------ 
2009-08-30 00:00:00.000 
2009-08-31 00:00:00.000 
2009-09-01 00:00:00.000 
2009-09-02 00:00:00.000 
2009-09-03 00:00:00.000 
2009-09-04 00:00:00.000 
2009-09-05 00:00:00.000 

（所影响的行数为 7 行） 
*/

--功能：找出在2个日期之间的日期
--startdate:2009年9月15日  endDate:2009年10月3日 

declare @startdate datetime,@enddate datetime
set @startdate='2009-08-30'
set @enddate='2009-09-05'

select convert(varchar(10),dateadd(day,number,@startdate),120) 
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>0 
    and type='p'

/*----------
2009-08-31
2009-09-01
2009-09-02
2009-09-03
2009-09-04
2009-09-05

(6 行受影响)

/*



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/24/4587448.aspx

#9

那位可以把#1的内容列一下步骤，详细些。 
并且改为计算两个时间内的有效工作时间（以小时为单位）


给出以后马上加分结贴 。 


谢谢各位 ！ 

#10

求将#1 的内容列出步骤， 并稍做讲解。。。

#11

小弟初学，多多关照 。  麻烦各位了 。。。

#12

master..spt_values 是一个系统表,表中提供了一个自然数序列(还有其他),用
select master..spt_values where type='p' 
就可以得到.

要产生一个连续的日期,可以用此表,比如,获得某天开始向前的连续10个日期,即可:

select convert(varchar(10),dateadd(d,-number,getdate()),120)dt from master..spt_values where type='p' and number<10
/*
dt
----------
2011-09-05
2011-09-04
2011-09-03
2011-09-02
2011-09-01
2011-08-31
2011-08-30
2011-08-29
2011-08-28
2011-08-27

(10 行受影响)

*/

#13

谢谢#12 

我想要个关于#1 的步骤 ，  即第一步执行哪个 ？ 

 我看着有些乱 。。。

谢谢

#14

1#列出了一些函数,要一个个看.

#15

是不是第一个函数汇总了下面的多个函数 、。