sql - 基于营业时间(上午 8 点 - 下午 5 点)而不是总小时数或天数的日期之间的 DATEDIFF

Question

我需要计算不同日期之间的营业时间(上午 8 点至下午 5 点)。这是场景:
票被分配给员工(Ticket_Submission(日期时间))，然后员工创建一个产品(Build_Date(日期时间))，现在有时为了创建一个药物需要建立一个药物(Med_Date(日期时间)) )。为了计算员工创建产品所需的时间，
我需要DATEDIFF之间Ticket_submission AND Build_Date但是，如果涉及药物治疗，则必须在 Med_Date AND Build_Date 之间.可以包括周末和节假日。以下帮助我获得天数差异:

.....
,IIF((med_date = buid_date OR ticket_submission = build_date), 1 
, IIF(med_date IS NULL OR med_date < ticket_submission , (DATEDIFF(dd, ticket_submission,build_date))
, DATEDIFF(dd, med_date, build_date))) AS buildcompletedate,….

这只会给我天数，而且，我知道如果不是 DD，我把小时数放在 2 个日期之间的总小时数。但是，我怎么能在营业时间拥有这些呢？

我很感激你的帮助。

ticketID  Ticket_submission               Med_Date                Build_Date
1549392    2017-04-07 10:31:06:210        2017-04-08 11:31:06:210  2017-04-09 12:30:08:110
1751406    2017-06-06 4:30:08:200        2018-08-06 3:30:08:200   2018-09-10 3:30:08:200 
2583870    2019-11-20 1:20:01:100        NULL                     2019-11-23 2:20:01:100          

为了帮助构建测试样本，这里有一个在内存中构建测试样本的通用表表达式:

; with Ticket (TicketID, Ticket_Submission, Med_Date, Build_Date)
AS
(
    SELECT TicketID = 1549392, Ticket_Submission = CAST('2017-04-07 10:31:06:210' AS DATETIME2), Med_Date = CAST('2017-04-08 11:31:06:210' AS DATETIME2), Build_Date = CAST('2017-04-09 12:30:08:110' AS DATETIME2) UNION ALL
    SELECT 1751406, CAST('2017-06-06 4:30:08:200' AS DATETIME2), CAST('2018-08-06 3:30:08:200' AS DATETIME2), CAST('2018-09-10 3:30:08:200 ' AS DATETIME2) UNION ALL
    SELECT 2583870, CAST('2019-11-20 1:20:01:100' AS DATETIME2), CAST(NULL AS DATETIME2), CAST('2019-11-23 2:20:01:100' AS DATETIME2)
)
select

    TicketID,
    0 AS RoundedBusinessHoursBetweenDates /* Update with answer code */
from Ticket

预期成绩

TicketID  RoundedBusinessHoursBetweenDates 
--------  --------------------------------
1549392   10
1751406   307
2583870   20????

四舍五入

可以调整时间

例如，将 4 小时 30 分钟改为 5 小时。

逐步了解计算

对于第一行(TicketID 1549392)，我希望看到:

10 小时(从上午 11:31 到下午 5 点(第一天)约 5 小时 30 分钟+上午 8 点到第二天 12:30 约 4.5 小时)

对于第二行(TicketID 1751406)，我希望看到:

第一天 2 小时(用于 med_build 日期)+ 最后一天 8 小时(上午 8 点至下午 3:30)+ 9 * 这两天之间的剩余天数

对于第三行(TicketID 2583870)，如果 Med_Date是 NULL那么只有从 ticket_submission 到 build_date 的区别

Answer 1

我不确定您的数据是否有效。我假设第二行的日期应该是 2018-09-10 15:30:08:200如 2018-09-10 3:30:08:200不在工作时间内。无论如何，您需要分 3 个不同的部分来计算小时数。从开始日期时间到下午 5 点的时间以及从最后一个日期的早上 8 点到实际最后一个日期时间的时间，加上两者之间的工作时间。

它并不漂亮，但这里是示例代码。这 3 列的总和是您的总小时数。

declare @tblTemp table(ticketId int, ticket_submission datetime, med_date datetime, build_date datetime)
insert into @tblTemp
values(1549392,'2017-04-07 10:31:06:210','2017-04-08 11:31:06:210','2017-04-09 12:30:08:110'),
    (1751406,'2017-06-06 16:30:08:200','2018-08-06 15:30:08:200','2018-09-10 15:30:08:200'),
    (2583870,'2019-11-20 13:20:01:100',null,'2019-11-23 14:20:01:100')

select ticketId, datediff(hour, coalesce(med_date, ticket_submission), convert(varchar(10), coalesce(med_date, ticket_submission), 120) + ' 17:00:00') -- first day hours
    ,(datediff(day, coalesce(med_date, ticket_submission), build_date) - 1) * (17-8) -- hours in between
    ,datediff(hour, convert(varchar(10), build_date, 120) + ' 08:00:00', build_date) -- last day hours
from @tblTemp

要排除周末，请参阅此 so answer或 this one .如果您想排除周末和节假日，您可能需要一个日历表。