sql - 计算SQL Server中记录的时间差

Question

我有一个名为 acttable1 的下表。

NUMBER TYPE           DATESTAMP            DESCRIPTION 
--------------------------------------------------------------------------------
10010  OPEN           2014-04-16 05:34:03  ASSIGNED TO GROUP 1
10010  REASSIGNMENT   2014-04-19 09:29:00  REASSIGNMENT FROM GROUP 1 TO GROUP 2
10010  REASSIGNMENT   2014-04-20 08:35:00  REASSIGNMENT FROM GROUP 2 TO GROUP 3
10010  CLOSED         2014-04-20 10:15:33  CLOSED

我需要计算总时间(DATESTAMP 列中每个值之间的时间差)ticket was with GROUP 1, GROUP 2, 第 3 组。

请帮助我构建一个 SQL 查询，以便我可以提取报告。

预期输出。

NUMBER  TYPE          DATESTAMP         Duration    DESCRIPTION
--------------------------------------------------------------------------------
10010   OPEN          04/19/14 00:45    00:00:00    ASSIGNED TO GROUP 1
10010   REASSIGNMENT  04/19/14 09:29    08:44:00    REASSIGNMENT FROM GROUP 1 TO GROUP 2
10010   REASSIGNMENT  04/20/14 07:29    22:00:00    REASSIGNMENT FROM GROUP 2 TO GROUP 3
10010   CLOSED        04/20/14 10:15    02:46:33    CLOSED

我正在使用 MS SQL 2008 数据库。

提前致谢。

Answer 1

我想这就是您要找的:

SELECT
    Number
    , Type
    , DateStamp
   , CAST(CAST(SECONDS/60/60%24 as VARCHAR) + ':'+ 
     CAST((SECONDS/60)%60 as VARCHAR) + ':' +
     CAST(SECONDS%60 as VARCHAR) AS TIME) AS TotalTime
    , Description
FROM
    (SELECT CurrentEvent.NUMBER
        ,CurrentEvent.TYPE
        ,CurrentEvent.DATESTAMP
        ,DATEDIFF(SECOND, CurrentEvent.DATESTAMP, ISNULL(NextEvent.DATESTAMP, CurrentEvent.DateStamp)) AS SECONDS
        ,CurrentEvent.DESCRIPTION
    FROM (
        SELECT NUMBER
            ,TYPE
            ,DATESTAMP
            ,DESCRIPTION
            ,ROW_NUMBER() OVER (PARTITION BY NUMBER ORDER BY DATESTAMP) Ordering
        FROM acttable1
        ) AS CurrentEvent LEFT JOIN (
        SELECT NUMBER
            ,DATESTAMP
            ,ROW_NUMBER() OVER (PARTITION BY NUMBER ORDER BY DATESTAMP) Ordering
        FROM acttable1
        ) AS NextEvent ON CurrentEvent.Ordering + 1 = NextEvent.Ordering
    ) AS FinalResultSet

基本思想是根据它们的 DATESTAMP 对结果进行排序，然后使用 LEFT JOIN 将两个连续的行放在同一行上，然后能够减去两个日期之间的时间差。

在最后一个查询的帮助下完成格式化之后，在计算出两个连续事件之间的持续时间(以秒为单位)之后。