工作天数和时间计算

create table tv_holiday(
HDate smalldatetime primary key clustered,
Name nvarchar(50) not null
)


指定时间段内的工作天数
create function f_Workday_thr(
@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_begin,@dt_end) + 1 -
(select COUNT(*) from tb_Holiday 
where HDate between @dt_end and @dt_begin
)))
end


指定日期加上工作天数
create function f_workdayAdd_four(
@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

计算工作时间的函数

if exists(select * from sysobjects where id = OBJECT_ID(N'[tb_worktime]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)
drop table [tb_worktime]
go


create table tb_worktime(
ID int identity(1,1) primary key,
time_start smalldatetime,
time_end smalldatetime,
worktime as datediff(minute,time_start,time_end)
)
go
if exists(select * from sys.sysobjects where id = OBJECT_ID(N'[dbo.f_wroktime]') and xtype in (N'FN',N'IF',N'TF'))
drop function [dbo].[f_worktime]
go
--计算两个日期之间的工作时间
create function f_worktime(
@date_begin datetime,
@date_end datetime
) returns int
as
begin
declare @worktime int
if DATEDIFF(day,@date_begin,@date_end) = 0
select @worktime = SUM(datediff(minute,
case when convert(varchar,@date_begin,108) > time_start
then convert(varchar,@date_begin,108)
else time_start end,
case when CONVERT(varchar,@date_end,108) < time_end
then convert(varchar,@date_end,108) 
else time_end end))
from tb_worktime
where time_end > CONVERT(varchar,@date_begin,108) and time_start < CONVERT(varchar,@date_end,108)


else


set @worktime = (select SUM(case
when convert(varchar,@date_begin,108) > time_start 
then datediff(minute,convert(varchar,@date_begin,108),time_end)
else worktime end)
from tb_worktime where time_end > CONVERT(varchar,@date_begin,108))
+(select SUM(case
when CONVERT(varchar,@date_end,108)< time_end
then datediff(minute,time_start,convert(varchar,@date_end,108))
else worktime end)
from tb_worktime
where time_start < convert(varchar,@date_end,108))
+ case when datediff(day,@date_begin,@date_end) > 1
then (datediff(day,@date_begin,@date_end) - 1)
* (select SUM(worktime) from tb_worktime)
else 0 end


return(@worktime)


end