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

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))>=@pm_end and  CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<=@am_str  --开始时间为上班开始时间，结束时间为下班时间
		set	@myresult=7.5
else if((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>=@am_str) and ((CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@pm_end)))
           begin
		
	
	    if ((CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>=@am_str  and   CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<=@am_end) and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>=@am_str  and   CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@am_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))>=@am_end and(CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<=@pm_str ))   and  (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>=@am_end and(CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@pm_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))>=@am_str) and (CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<=@pm_str))and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>=@am_end and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@pm_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))>=@am_str) and (CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@pm_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))>=@am_str)  and  (CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<=@am_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))>=@pm_str)and (CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<=@pm_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))>=@am_end and CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))<=@pm_str
                   set @first=4
				   else if CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>=@am_end   and CONVERT( decimal(18,2),DATEPART(HOUR,@str_time))>=@pm_str
				   set @first=0 
 if(CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>=@am_str and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@am_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))>=@am_end and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@pm_str
set @end=3.5
   else if CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>=@am_end   and CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>=@pm_str
				   set @end=0 
else if ((CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))>=@pm_str) and ((CONVERT( decimal(18,2),DATEPART(HOUR,@end_time))<=@pm_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 = @end+@first+(CONVERT( decimal(18,2)  ,DATEPART(DAY,@end_time))+ -CONVERT( decimal(18,2)  ,DATEPART(DAY,@str_time))-1)*7.5
 else
 set @myresult = @end+@first
	end
	return  @myresult
end
GO

初始数据

效果