DATEDIFF的营业时间和时间仅限天数 [英] DATEDIFF in Business Working Hours & Days Only

问题描述

我试图写一份报告，但有点卡住:/我试图显示两个日期之间的小时和分钟数，但减去非工作时间.

I am trying to write a report and am a little stuck :/ I am trying to show the hours and minutes between two dates however minus the non business working hours.

例如，某公司在工作日的工作时间为08:00至17:00，今天在16:00记录了一个呼叫，明天在16:00关闭，那么它将减去24小时，从而减去了24小时的工作时间，因此可以在9个小时内正常工作

Example a business works weekdays between 08:00 until 17:00 and a call was logged at 16:00 today and closed tomorrow at 16:00 so that would be 24 hours minus the business hours so would work out at 9 hours.

我还创建了一个单独的表，其中包含除周末，工作日开始以及工作日结束之外的所有年份.但是我仍然坚持找出没有非工作时间的时间.

I have also created a seperate table which holds all the days of the years except weekends and the start of the business working day along with the end of the business working day. But I am still stuck with finding out the hours between without the non business hours.

示例数据:

Call_Initiated - Call_Ended
10/05/2013 15:00 - 13/05/2013 13:00

我想要的结果

Call_Initiated - Call_Ended - Time_To_Resolve
10/05/2013 15:00 - 13/05/2013 13:00 - 07

推荐答案

我只是对您的问题感到好奇，所以做到了.

I was just curious about your problem and made this.

也许不是最好的脚本，但是它可能会给您一些有关如何解决问题的想法.

Maybe not the best script, but it might give you some ideas on how to tackle the problem.

它具有全部功能，但是我生成了日期，您可能要使用您的日程表.

It is fully functional, but I generated the dates and you might want to use your day-table.

declare @callLogStart datetime = '2013-01-04 16:00'
declare @callLogEnd datetime = '2013-01-08 09:00'

;with dates(startDate, endDate)
as
(
select  cast('2013-01-01 08:00' as datetime)
        ,cast('2013-01-01 17:00' as datetime)
union all
select  DATEADD(day,1, startDate)
        ,DATEADD(day, 1, endDate)
from    dates
where   startDate < '2013-02-01 08:00'
)
,startDay
as
(
    select  *
            ,Datediff(hour, d.startDate, d.endDate) - DATEDIFF(hour, startDate, @callLogStart) as spent
    from    dates d
    where   @callLogStart between d.startDate and d.endDate
)
,endDay
as
(
    select  *
            ,Datediff(hour, d.startDate, d.endDate) - datediff(hour, @callLogEnd, endDate) as spent
    from    dates d
    where   @callLogEnd between d.startDate and d.endDate
)

select  --SUM(spent) as actualTime
        spent
        ,startDate
        ,endDate
        ,mark
from
(
    select  startDate
            ,endDate
            ,spent
            ,'start' as mark 
    from    startDay
    union
    select  startDate
            ,endDate
            ,spent
            ,'end'
    from    endDay
    union
    select  s.startDate
            ,s.endDate
            ,-Datediff(hour, s.startDate, s.endDate)
            ,'remove'
    from    startDay s
    join    endDay e
        on  s.startDate = e.startDate
        and s.endDate = e.endDate
    union
    select  startDate
            ,endDate
            ,Datediff(hour, startDate, endDate)
            ,'between'
    from    dates
    where   @callLogStart < startDate
    except
    select  startDate
            ,endDate
            ,Datediff(hour, startDate, endDate)
            ,'between'
    from    dates
    where   @callLogEnd < endDate
) x
order by    
    case mark 
        when 'start' then 0 
        when 'between' then 1 
        when 'end' then 2 
        when 'remove' then 3 
    end

希望有帮助

这篇关于DATEDIFF的营业时间和时间仅限天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！