一般性工作时间计算存储过程(SQL SERVER)
在统计工作效率的时候,只计算工作时候非常重要
写了一个一般性工作时间计算的函数
CREATE FUNCTION fun_GetWorkMinute
(
@startDate DATETIME ,
@endDate DATETIME
)
RETURNS int
AS
begin
declare @DayMinute int,@s_Minute int,@s_Minute2 int
set @DayMinute = 7*60 --一天工作7个小时
set @s_Minute = 11*60 + 30 --上午11:30下班
set @s_Minute2 = 14*60 --下午14上班
declare @minute int,@day int,@totalDay int
set @minute = 0
if @startDate is not null and @endDate is not null
begin
set @totalDay = datediff(day,@startDate,@endDate)
set @day = @totalDay/7 ---周
if @day > 0
set @minute = @minute + @day*5* @DayMinute
declare @sM int,@sM2 int
set @sM = datepart(hour,@startDate)*60 + datepart(minute,@startDate)
set @sM2 = datepart(hour,@endDate)*60 + datepart(minute,@endDate)
set @day = @totalDay - @day * 7
if @day > 0
set @minute = @minute + @day* @DayMinute
--同一个上下午段,非工作时间也类同
if (@sM >= @s_Minute2 AND @sM2 >= @s_Minute2) or (@sM <= @s_Minute AND @sM2 <= @s_Minute)
set @minute = @minute - @sM + @sM2
else if (@sM >= @s_Minute2) --上午到下午
set @minute = @minute - @sM + @sM2 + @s_Minute2 - @s_Minute
else --下午到下一天的上午
set @minute = @minute - @sM + @sM2 - @s_Minute2 + @s_Minute
end
return @minute
end
(
@startDate DATETIME ,
@endDate DATETIME
)
RETURNS int
AS
begin
declare @DayMinute int,@s_Minute int,@s_Minute2 int
set @DayMinute = 7*60 --一天工作7个小时
set @s_Minute = 11*60 + 30 --上午11:30下班
set @s_Minute2 = 14*60 --下午14上班
declare @minute int,@day int,@totalDay int
set @minute = 0
if @startDate is not null and @endDate is not null
begin
set @totalDay = datediff(day,@startDate,@endDate)
set @day = @totalDay/7 ---周
if @day > 0
set @minute = @minute + @day*5* @DayMinute
declare @sM int,@sM2 int
set @sM = datepart(hour,@startDate)*60 + datepart(minute,@startDate)
set @sM2 = datepart(hour,@endDate)*60 + datepart(minute,@endDate)
set @day = @totalDay - @day * 7
if @day > 0
set @minute = @minute + @day* @DayMinute
--同一个上下午段,非工作时间也类同
if (@sM >= @s_Minute2 AND @sM2 >= @s_Minute2) or (@sM <= @s_Minute AND @sM2 <= @s_Minute)
set @minute = @minute - @sM + @sM2
else if (@sM >= @s_Minute2) --上午到下午
set @minute = @minute - @sM + @sM2 + @s_Minute2 - @s_Minute
else --下午到下一天的上午
set @minute = @minute - @sM + @sM2 - @s_Minute2 + @s_Minute
end
return @minute
end
对于五一,十一没有作处理,需要加工作日管理的模块