T O P

[资源分享]     按8小时制计算的工作时长统计函数

  • By - 楼主

  • 2021-09-08 18:00:24
  • 需求:

    根据结束时间和开始时间统计任务的工作时长

    思路:

     

     

    代码:

      1 CREATE FUNCTION [dbo].[Fn_CountWorkTime_Books_V2]
      2 (@startDate datetime , @endDate datetime)
      3 RETURNS float
      4 as
      5 BEGIN
      6     if(@startDate> @endDate)
      7     begin
      8         return 0
      9     end
     10     
     11     declare @returnValue float -- 返回结果(天)
     12     declare @returnInt Int -- 返回工作时长(秒)
     13     declare @startDateInt int -- 开始时间时间范围
     14     declare @endDateInt int -- 结束时间时间范围
     15     declare @isHolidays int -- 是否为节假日,周六日
     16     declare @startDateReturn int -- 非同一天,开始当天工作时长
     17     declare @endDateReturn int -- 非同一天,结束当天工作时长
     18     declare @middleReturn int -- 非同一天,中间工作时长
     19     
     20     begin
     21         -- 开始时间时间范围
     22         if(@startDate < DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@startDate))))
     23         begin
     24             set @startDateInt = 2
     25             set @startDateReturn = 8*60*60            
     26         end
     27         else if(@startDate>= DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@startDate))) and @startDate<= DATEADD(HOUR,12,dbo.GETCURDAY(@startDate)))
     28         begin
     29             set @startDateInt = 2
     30             set @startDateReturn = DATEDIFF(ss,@startDate,DATEADD(HOUR,12,dbo.GETCURDAY(@startDate))) + 4.5*60*60
     31         end
     32         else if(@startDate>DATEADD(HOUR,12,dbo.GETCURDAY(@startDate)) and @startDate< DATEADD(HOUR,13,dbo.GETCURDAY(@startDate)))
     33         begin
     34             set @startDateInt = 4
     35             set @startDateReturn = 4.5*60*60
     36         end
     37         else if(@startDate>=DATEADD(HOUR,13,dbo.GETCURDAY(@startDate)) and @startDate<=DATEADD(n,30,DATEADD(HOUR,17,dbo.GETCURDAY(@startDate))) )
     38         begin
     39             set @startDateInt = 4
     40             set @startDateReturn = DATEDIFF(ss,@startDate,DATEADD(n,30,DATEADD(HOUR,17,dbo.GETCURDAY(@startDate))))
     41         end
     42         else
     43         begin
     44             set @startDateInt = 4
     45             set @startDateReturn = 0
     46         end
     47         -- 结束时间时间范围
     48         if(@endDate < DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@endDate))))
     49         begin
     50             set @endDateInt = 2    
     51             set @endDateReturn = 0
     52         end
     53         else if(@endDate>= DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@endDate))) and @endDate<= DATEADD(HOUR,12,dbo.GETCURDAY(@endDate)))
     54         begin
     55             set @endDateInt = 2
     56             set @endDateReturn = DATEDIFF(ss,DATEADD(HOUR,8,dbo.GETCURDAY(@endDate)),@endDate)
     57         end
     58         else if(@endDate>DATEADD(HOUR,12,dbo.GETCURDAY(@endDate)) and @endDate< DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)))
     59         begin
     60             set @endDateInt = 4
     61             set @endDateReturn = 3.5*60*60
     62         end
     63         else if(@endDate>=DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)) and @endDate<=DATEADD(n,30,DATEADD(HOUR,17,dbo.GETCURDAY(@endDate))) )
     64         begin
     65             set @endDateInt = 4
     66             set @endDateReturn = DATEDIFF(ss,DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)),@endDate)+3.5*60*60
     67         end
     68         else
     69         begin
     70             set @endDateInt = 4
     71             set @endDateReturn = 8*60*60
     72         end
     73     end
     74         
     75     -- 判断是否为同一天
     76     if(convert(varchar(10), @startDate,23) = convert(varchar(10), @endDate,23))
     77     begin
     78         --判断是否为节假日
     79         select @isHolidays=count(1) from 节假日配置表  where 日期=convert(varchar(10), @startDate,23)
     80         if(@isHolidays!=0)
     81         begin
     82             set @returnInt = 0
     83         end
     84         else
     85         begin
     86             if(@startDateInt = 2 and @startDateInt=@endDateInt)
     87             begin
     88                 -- 结束-开始
     89                 set @returnInt = DATEDIFF(ss,@startDate,@endDate)
     90             end
     91             else if(@startDateInt = 2 and @endDateInt = 4)
     92             begin
     93                 -- (12点-开始)+(结束-13)
     94                 set @returnInt = DATEDIFF(ss,@startDate,DATEADD(HOUR,12,dbo.GETCURDAY(@endDate))) + DATEDIFF(ss,DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)),@endDate)
     95             end
     96             else
     97             begin
     98                 -- 结束-开始
     99                 set @returnInt = DATEDIFF(ss,@startDate,@endDate)
    100             end
    101         end
    102     end
    103     else
    104     begin
    105         -- 不为同一天,工作时长=开始当天工作时长+结束当天工作时长+中间工作时长    
    106         -- 中间工作时长
    107         declare @totalDays int
    108         declare @totalUnWorkDay int
    109         set @totalDays = DATEDIFF(day,@startDate,@endDate)
    110         if(@totalDays > 1)
    111         begin
    112             select @totalUnWorkDay=count(1) from 节假日配置表 where 日期>@startDate and 日期<@endDate
    113             set @middleReturn = (@totalDays-1-@totalUnWorkDay)*8*60*60
    114         end
    115         else
    116         begin
    117             set @middleReturn = 0
    118         end
    119         
    120         set @returnInt = @startDateReturn+@endDateReturn+@middleReturn
    121     end
    122     
    123     set @returnValue = cast(cast(@returnInt as float)/cast(28800 as float) as float(4))
    124     return @returnValue
    125 end

     

     

    ps: 网上实在是搜不到8小时制统计工作时长的函数,特此将自己的解决思路分享出来,希望能帮助到其他人. 如果你和更好的解决思路 ,欢迎讨论.

    本帖子中包含资源

    您需要 登录 才可以下载,没有帐号?立即注册