[请教]计算工作日和工作时间算法

[wys1978]

手上的项目根据用户需求，要做几份报表，需要计算到2个时间之间间隔的工作日和工作小时，准备写2个function：
WORKDAYDIFF(@fromDate datetime, @thruDate datetime) RETURN int
WORKHOURDIFF(@fromDate datetime, @thruDate datetime) RETURN int

WORKDAYDIFF需要把周六和周日排除掉
WORKHOURDIFF需要把每天17:30以后和每天08:30以前的时间排除掉(当然周六和周日的整天都要排除）

我碰到的问题是很难找出一个简洁明了的算法来做这样的2个function，在写了一堆的if else以后，准备放弃了，因为没有信心能够保证写的if else完整地覆盖所有的情况，我想应该有人做过类似的功能，能否指点一下？谢谢！

[wzh1215]

--工作日：
declare @begindate datetime,@enddate datetime,@days int
set @begindate=getdate()-30
set @enddate=getdate()
set @days=0
while @begindate<=@endate
begin
if (datepart(DW,@begindate)=1) or (datepart(DW,@begindate)=7)
set @days=@days+1
set @begindate=@begindate+1
end
--工作时只要用@days乘以工作时就行了！

[gmlxf]

create function WORKDAYDIFF(@fromDate datetime, @thruDate datetime)
RETURNS int
as
begin
declare @ftDate datetime,@num int
set @num=0
set @ftDate=@fromDate
while @ftDate<=@thruDate
begin
if datepart(dw,@ftDate) not in (1,7)
set @num=@num + 1
set @ftDate=@ftDate + 1
end
return @num
end
go
select dbo.WORKDAYDIFF('2003-12-19','2003-12-21')

[gmlxf]

create function WORKDAYDIFF(@fromDate datetime, @thruDate datetime)
RETURNS int
as
begin
declare @num int
set @num=0
while @fromDate<=@thruDate
begin
if datepart(dw,@fromDate) not in (1,7)
set @num=@num + 1
set @fromDate=@fromDate + 1
end
return @num
end
go
select dbo.WORKDAYDIFF('2003-12-19','2003-12-21')

[gmlxf]

create function WORKHOURDIFF(@fromDate datetime, @thruDate datetime)
RETURNS int
as
begin
declare @hourNums int
select @hourNums=dbo.WORKDAYDIFF(@fromDate,@thruDate) * (24 - 15)
return @hourNums
end
go

select dbo.WORKHOURDIFF('2003-12-19','2003-12-21')

[playyuer]

create function WORKDAYDIFF(@fromDate datetime, @thruDate datetime)
RETURNS int
as
begin
return datediff(day,@fromDate,@thruDate)
- datediff(week,@fromDate,@thruDate)
+ case when datepart(weekday,@fromDate) > 1 then 0 else 1 end
- case when datepart(weekday,@thruDate) >= 1 then 0 else 1 end
- datediff(week,@fromDate,@thruDate)
+ case when datepart(weekday,@fromDate) > 7 then 0 else 1 end
- case when datepart(weekday,@thruDate) >= 7 then 0 else 1 end
end

go

create function WORKHOURDIFF(@fromDate datetime, @thruDate datetime)
RETURNS int
as
begin
return dbo.WORKDAYDIFF(@fromDate,@thruDate) * 8
end

参考:
请问有没有统计一个时间段星期几的个数的函数？急、、、、、
http://expert.csdn.net/Expert/TopicView1.asp?id=2585071

[wys1978]

不好意思，可能我没有说清楚，我的fromDate和thruDate都是带时间的，而不仅仅是一个date。
比如：
select dbo.WORKDAYDIFF('2003-12-15 09:30:00','2003-12-16 09:15:00') = 0
select dbo.WORKDAYDIFF('2003-12-15 09:30:00','2003-12-16 09:45:00') = 1

WORKDAYDIFF我可以参考playyuer(双规干部) 的算法，修改一下datediff(day,@fromDate,@thruDate)到datediff(hour, @fromDate, @thruDate) / 24就可以了。谢谢playyuer提供的算法，很简洁。

但是麻烦的是WORKHOURDIFF （假设08：30 ~ 17:30为上班时间）:
select dbo.WORKHOURDIFF('2003-12-15 09:30:00','2003-12-16 09:30:00') = 9
select dbo.WORKHOURDIFF('2003-12-15 09:30:00','2003-12-16 07:00:00') = 8
还需要考虑开始时间和结束时间都可能为周末的问题，总共有16种排列组合，按照WORKDAYDIFF的方式（4种排列组合）去做的话，感觉代码很冗长，所以请教大家有没有更好的算法。

[playyuer]

declare @fromDate datetime,@thruDate datetime
set @fromDate = '2001-04-01 08:31:00'
set @thruDate = '2001-04-30 17:29:00'

select (datediff(day,@fromDate,@thruDate)
- datediff(week,@fromDate,@thruDate)
+ case when datepart(weekday,@fromDate) > 1 then 0 else 1 end
- case when datepart(weekday,@thruDate) >= 1 then 0 else 1 end
- datediff(week,@fromDate,@thruDate)
+ case when datepart(weekday,@fromDate) > 7 then 0 else 1 end
- case when datepart(weekday,@thruDate) >= 7 then 0 else 1 end
-2) * 9 as 小时

,datediff(minute,
case when @fromDate >= dateadd(minute,8 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))
and @fromDate <= dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))
then @fromDate
when @fromDate > dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))
then dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))
else dateadd(minute,8 * 60 + 30 ,dateadd(day,datediff(day,0,@fromDate),0)) end
,dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))) as 开始日期分钟数
,
datediff(minute
,dateadd(minute,8 * 60 + 30 ,dateadd(day,datediff(day,0,@thruDate),0))
,case when @thruDate <= dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0))
and @thruDate >= dateadd(minute,8 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0))
then @thruDate
when @thruDate < dateadd(minute,8 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0))
then dateadd(minute,8 * 60 + 30 ,dateadd(day,datediff(day,0,@thruDate),0))
else dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0)) end) as 结束日期分钟数

[edchild]

学习!

[wys1978]

Hi playyuer(双规干部),
计算出开始日期分钟数, 结束日期分钟数以后，怎么用来计算工作时间间隔呢？
如果开始日期或者结束日期是周末，周日，这些时间又需要过滤掉，怎么处理比较方便呢？

[DreamManor]

学习