DATEDIFF的营业时间和时间仅限天数

本文介绍了DATEDIFF的营业时间和时间仅限天数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

希望有帮助