列的秒表

Question

在我们的组织帮助台中，我们想要计算从创建票证开始所花费的时间。我们有许多地点，具有不同的工作时间设置。有些地点全天候工作，有些地点从上午9点工作到下午5点。每天早上，我需要运行一个查询，以获取年龄超过8小时的门票，并对其执行一些操作。在SQL Server中是否有秒表列或任何其他选项，通过这些选项，年龄会以分钟为单位自动递增，以便每当我们进行查询时，我们都可以获得自创建以来的确切年龄。

例如，如果位置是分支机构，工单创建于2020/07/10 15:00 PM，如果在2020/07/11 09:00 AM执行查询，则每个运营时间(上午9:00到下午5:00)的年龄为2小时。如果票证的地点是总公司，则运营时间为24x7，票证的使用期限为18小时。

添加了用于处理此信息的3列。

IS_TIMER_RUNNING : true | false true:在运营时间(上午9点到下午5点)。false:非工作时间(下午5点到上午9点)。TIMER_RUNNING_TIME : datetime TICKET_AGE :以秒为单位的时间

在上午9点使用作业时，IS_TIMER_RUNNING将设置为true。在下午5点的作业中，IS_TIMER_RUNNING将设置为false，并且TICKET_AGE将填充以秒为单位的时间，直到该时间结束。

我尝试了下面的查询，它给出了预期的查询。

SELECT 
  TICKETID, 
  CONVERT(
    VARCHAR, 
    DATEADD(
      s, 
      CASE WHEN IS_TIMER_RUNNING = '1' THEN datediff(
        s, 
        dateadd(
          s, 
          (wo.TIMER_RUNNING_TIME / 1000), 
          '1970-01-01 00:00:00'
        ), 
        GETUTCDATE()
      ) + TICKET_AGE ELSE TICKET_AGE END, 
      '1900/01/01'
    ), 
    108
  ) 
FROM 
  TICKET

如果有其他更好的选择，敬请指教

Answer 1

您可以创建一个包含所有营业时间(考虑周末、节假日和不同工作时间安排)的表。之后，您可以将这个带有工作时间的表加入到您的工单中(考虑到适用的时间表)，然后使用DATEDIFF来计算经过的时间。

例如，创建“营业时间表”

SET DATEFIRST 1;

WITH CTE
AS
(
SELECT cast('2020-01-01' as datetime) as theDate
UNION ALL
SELECT dateadd(day,1,theDate)
FROM CTE
WHERE theDate<='2020-12-31'
)
SELECT 1 as BusinessHoursId,DateAdd(hh,9,theDate) as SOB,DATEADD(hh,17,theDate) as EOB
into Businesshours
FROM CTE
WHERE DATEPART(DW,theDate) between 1 and 5
option (maxrecursion 0)

然后，您可以在联接中使用此表：

SELECT TicketId,
       SUM(DATEDIFF(n,
               CASE WHEN t.Created > bh.SOB THEN t.Created ELSE bh.SOB END ,
               CASE WHEN t.Closed < bh.EOB THEN t.Closed ELSE bh.EOB END
               )
          )
         
FROM Tickets t
JOIN Businesshours bh
ON t.Created < bh.EOB
AND t.Closed > bh.SOB
GROUP BY TicketId