工作日时间差/考勤计算(SQL 自定义函数)

工作日时间差/考勤计算(SQL 自定义函数)
问题说明：给出两个时间（开始时间，结束时间），算出这个两个时间 的工作日时间差，公司上午上班时间8:00 - 11:30，下午上班时间：13:00- 17:00，即只在这个排班时间 间的时间差）
备注：该函数未涉及节假日的条件

CREATE  FUNCTION  [dbo].[WorkTime2](@str_time datetime, @end_time datetime) 
RETURNS decimal(18,2)
AS
BEGIN
declare @am_str decimal(18,2), @am_end decimal(18,2) 
		,@pm_str decimal(18,2) ,@pm_end decimal(18,2)
		,@myresult decimal(18,2)
		,@first decimal(18,2)
		,@end decimal(18,2)
--定义工作日上班时间段
set @am_str=8  set @am_end=11.5
set @pm_str=13 set @pm_end=17

if(CONVERT(VARCHAR(10),@str_time, 120) = CONVERT(VARCHAR(10),@end_time, 120))  --是否是同一天
begin
if CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_end and  CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<[e-mail]_str  --开始时间为上班开始时间，结束时间为下班时间
		set	@myresult=7.5
else if((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_str) and ((CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_end)))
           begin
		
	
	    if ((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_str  and   CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<[e-mail]_end) and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_str  and   CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_end) )
	  set @myresult=CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))+(CONVERT( decimal(18,2),DATEPART(MINUTE,@end_time))/60)-CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))-(CONVERT( decimal(18,2),DATEPART(MINUTE,@str_time))/60)
else if((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_end and(CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<[e-mail]_str ))   and  (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_end and(CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_str ) ))
	
		set @myresult=0
	else	if ((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>@am_str and (CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<@pm_str))and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>@pm_str and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<@pm_end))

        set @myresult=CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))+(CONVERT( decimal(18,2),DATEPART(MINUTE,@end_time))/60)-CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))-1.5-(CONVERT( decimal(18,2),DATEPART(MINUTE,@str_time))/60)

	 else  if ((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_str) and (CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<[e-mail]_str))and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_end and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_str)
			set  @myresult=11.5-CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))-(CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))/60)
 else if( ((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_str) and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_str)) or ((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>@pm_str)and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<@pm_end)))
                  set @myresult=CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))+(CONVERT( decimal(18,2),DATEPART(MINUTE,@end_time))/60)-CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))-(CONVERT( decimal(18,2),DATEPART(MINUTE,@str_time))/60)
			end

end

  else
  begin
  if((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_str)  and  (CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<[e-mail]_end))
                  set @first=17-CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))-1.5-(CONVERT( decimal(18,2),DATEPART(MINUTE,@str_time))/60)
        
		else if((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_str)and (CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<[e-mail]_end))
                  set @first=17-CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))-(CONVERT( decimal(18,2),DATEPART(MINUTE,@str_time))/60)
        else if CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_end and CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<[e-mail]_str
                   set @first=4
				   else if CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_end   and CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>[e-mail]_str
				   set @first=0 
 if(CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_str and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_end)
set @end=CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))-8+(CONVERT( decimal(18,2),DATEPART(MINUTE,@end_time))/60)
else if CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_end and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_str
set @end=3.5
   else if CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_end   and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_str
				   set @end=0 
else if ((CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>[e-mail]_str) and ((CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<[e-mail]_end)))
begin
  set @end=17-CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))-1.5-(CONVERT( decimal(18,2),DATEPART(MINUTE,@str_time))/60)
  end
  --判断天数大于1 比如  25-23 
  if (CONVERT( decimal(18,2)  ,DATEPART(DAY,@end_time)) -CONVERT( decimal(18,2)  ,DATEPART(DAY,@str_time))-1)>=1
set @myresult = @[e-mail]+(CONVERT( decimal(18,2)  ,DATEPART(DAY,@end_time))+ -CONVERT( decimal(18,2)  ,DATEPART(DAY,@str_time))-1)*7.5
 else
 set @myresult = @[e-mail]
	end
	return  @myresult
end
GO

初始数据

效果