/*
* 请假两时间的小时数差
*/
create PROCEDURE [dbo].[os_vocation_hour]
@starttime varchar(20), --开始时间
@endtime varchar(20) --结束时间

AS
if(isdate(@starttime)=0 or isdate(@endtime)=0) --非日期格式
begin
select 0
end
else --正常日期格式
begin
declare @day_jg varchar(20),@day_num float,@hour_num float,@hour_temps float,@hour_tempe float -- 声明三个变量
declare @s1 datetime,@e1 datetime,@num_old float

set @hour_temps=DatePart(Hour,@starttime) --获取开始小时
set @hour_tempe=DatePart(Hour,@endtime) --获取结束小时

if(@hour_temps>=8 and @hour_temps<=17 and @hour_tempe>=8 and @hour_tempe<=17) --白班
begin
select @hour_num=datediff(hour,cast(@starttime as datetime),cast(@endtime as datetime)) -- 获取两时间的小时数的差
select @day_num=datediff(day,cast(@starttime as datetime),cast(@endtime as datetime)) -- 获取两时间的天数的差
if(@day_num=0 and @hour_num>0) --当天
begin
--同天同一上午或同一下午
if((@hour_temps>=8 and @hour_temps<=12 and @hour_tempe>=8 and @hour_tempe<=12) or (@hour_temps>=13 and @hour_temps<=17 and @hour_tempe>=13 and @hour_tempe<=17) )
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))
else if @hour_temps>=8 and @hour_temps<=12 and @hour_tempe>=13 and @hour_tempe<=17 --开始在上午，结束在下午
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60
else --时间段不对
set @day_jg=-1000
end
else if(@day_num=1) --隔天
begin
if(@hour_temps>=8 and @hour_temps<=12 and @hour_tempe>=8 and @hour_tempe<=12) --开始在上午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15
end
else if(@hour_temps>=8 and @hour_temps<=12 and @hour_tempe>=13 and @hour_tempe<=17) --开始在上午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-60
end
else if(@hour_temps>=13 and @hour_temps<=17 and @hour_tempe>=8 and @hour_tempe<=12) --开始在下午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60*15
end
else if(@hour_temps>=13 and @hour_temps<=17 and @hour_tempe>=13 and @hour_tempe<=17) --开始在下午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15
end
end
else if(@day_num>1) --多天
begin
if(@hour_temps>=8 and @hour_temps<=12 and @hour_tempe>=8 and @hour_tempe<=12) --开始在上午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-(@day_num-1)*24*60
end
else if(@hour_temps>=8 and @hour_temps<=12 and @hour_tempe>=13 and @hour_tempe<=17) --开始在上午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-60-(@day_num-1)*24*60
end
else if(@hour_temps>=13 and @hour_temps<=17 and @hour_tempe>=8 and @hour_tempe<=12) --开始在下午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60*15-(@day_num-1)*24*60
end
else if(@hour_temps>=13 and @hour_temps<=17 and @hour_tempe>=13 and @hour_tempe<=17) --开始在下午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-(@day_num-1)*24*60
end
--处理小时 --处理天数
set @num_old=@day_num
set @s1=@starttime

while(@s1<=@endtime) --用while循环判断是否周末
begin
if(datename(weekday,@s1)=‘星期六‘ or datename(weekday,@s1)=‘星期日‘) --是周末,则天数减1
set @day_num=@day_num-1
set @s1=dateadd(day,1,@s1) --下一天
end
set @day_num=@day_num-1 --天数少1
set @day_jg=@day_jg+@day_num*8*60 --得出分钟数
end

end
else if((@hour_temps>=20 and @hour_temps<=24 or @hour_temps>=0 and @hour_temps<=5) and (@hour_tempe>=20 and @hour_tempe<=24 or @hour_tempe>=0 and @hour_tempe<=5)) --夜班
begin
set @starttime=dateadd(Hour,-12,@starttime) --提前12小时
set @endtime=dateadd(Hour,-12,@endtime)
select @day_num=datediff(day,@starttime,@endtime) -- 获取两时间的天数的差
select @hour_num=datediff(hour,cast(@starttime as datetime),cast(@endtime as datetime))
set @hour_temps=DatePart(Hour,@starttime) --获取开始小时
set @hour_tempe=DatePart(Hour,@endtime) --获取结束小时

if(@day_num=0 and @hour_num>0) --当天
begin
--同天同一上午或同一下午
if((@hour_temps>=8 and @hour_temps<=13 and @hour_tempe>=8 and @hour_tempe<=13) or (@hour_temps>=14 and @hour_temps<=17 and @hour_tempe>=14 and @hour_tempe<=17))
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))
else if @hour_temps>=8 and @hour_temps<=13 and @hour_tempe>=14 and @hour_tempe<=17
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60
else --时间段不对
set @day_jg=-1000
end
else if(@day_num=1) --隔天
begin
if(@hour_temps>=8 and @hour_temps<=13 and @hour_tempe>=8 and @hour_tempe<=13) --开始在上午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15
end
else if(@hour_temps>=8 and @hour_temps<=13 and @hour_tempe>=14 and @hour_tempe<=17) --开始在上午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-60
end
else if(@hour_temps>=14 and @hour_temps<=17 and @hour_tempe>=8 and @hour_tempe<=13) --开始在下午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60*15
end
else if(@hour_temps>=14 and @hour_temps<=17 and @hour_tempe>=14 and @hour_tempe<=17) --开始在下午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15
end
end
else if(@day_num>1) --多天
begin
if(@hour_temps>=8 and @hour_temps<=13 and @hour_tempe>=8 and @hour_tempe<=13) --开始在上午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-(@day_num-1)*24*60
end
else if(@hour_temps>=8 and @hour_temps<=13 and @hour_tempe>=14 and @hour_tempe<=17) --开始在上午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-60-(@day_num-1)*24*60
end
else if(@hour_temps>=14 and @hour_temps<=17 and @hour_tempe>=8 and @hour_tempe<=13) --开始在下午，结束在上午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60*15-(@day_num-1)*24*60
end
else if(@hour_temps>=14 and @hour_temps<=17 and @hour_tempe>=14 and @hour_tempe<=17) --开始在下午，结束在下午
begin
set @day_jg=datediff(mi,cast(@starttime as datetime),cast(@endtime as datetime))-60-60*15-(@day_num-1)*24*60
end
--处理小时 --处理天数

set @num_old=@day_num
set @s1=@starttime

while(@s1<=@endtime) --用while循环判断是否周末
begin
if(datename(weekday,@s1)=‘星期六‘ or datename(weekday,@s1)=‘星期日‘) --是周末
set @day_num=@day_num-1
set @s1=dateadd(day,1,@s1) --下一天
end
set @day_num=@day_num-1
set @day_jg=@day_jg+@day_num*8*60
end
end
else --有问题的时间
begin
set @day_jg=-1000
end
set @day_num=@day_jg/60/8 --从分钟数得出天数
if(@day_jg>0 and @day_jg<8*60) --不足8小时,算0.5天
set @day_num=0.5
else if(@day_jg>8*60 and (@day_jg /60) % 8 !=0) --超过8小时,被8整除,有余的,加0.5天
set @day_num=@day_num+0.5
declare @t float
set @t= cast((cast(@day_jg as decimal(10,3))/60) as decimal(10,1)) --时数
select case when @t-floor(@t)>0 and @t-(floor(@t)+0.5)<=0 then (floor(@t)+0.5) when @t-(floor(@t)+0.5)>0 then (floor(@t)+1) else floor(@t) end
end

请假两时间的小时数差