如何调整下面的查询，以在SQL中显示转换到下一个状态的时间？

Question

下面的透视表查询仅按状态收集花费的时间。例如，如果状态为NEW (按customer_update_date升序排序-按票据编号分组)，则查询不会获得转换到下一状态的时间(因为在下次更新不同状态之前，它实际上处于相同状态)。

假设此示例处于新状态，从8/23/16 01:00:00 PM到8/23/16 02:00:00 PM (1小时)。但是，从NEW变为PENDING的更新序列中，时间戳是8/23/16 04:00:00 PM，这意味着它仍然在NEW中额外停留了2个小时。

有没有可能对下面的查询进行调整，以添加从状态到状态的转换时间，并根据先前显示的状态对该额外时间进行分组？我希望这是有意义的。非常感谢您的宝贵时间。

WITH t AS (
    select 
        ticket_number,
        ISNULL(status,'null') status,
        update_date,
        row_number() OVER (PARTITION BY ticket_number ORDER BY update_date) rn
    from xxx.dbo.report
), s AS (
    SELECT 
        t1.ticket_number,
        t1.status,
        t1.update_date,
        t2.update_date prevdate,
        case when t2.status=t1.status then DATEDIFF(s, t2.update_date, t1.update_date) end dif
    FROM t t1
    LEFT JOIN t t2 ON t1.ticket_number=t2.ticket_number AND t1.rn=t2.rn+1
)
SELECT *
FROM (
    SELECT
        ticket_number,
        [status],
        cast(dateadd(s,sum(dif),0) as time(0)) [time]
    FROM s
    GROUP BY ticket_number,status )  src
PIVOT
(
  min([time])
  for [status] in ([Closed], [Complete], [New], [Waiting-1], [Waiting-2], [Waiting-3], [StopTime], [Research-1], [Research-2], [Resolved], [Special Request], [Opened Request], [null])
) piv

Answer 1

似乎您想要每个状态的最小行，然后计算这两行之间的时间。下面的代码将完成此操作，并提供一些示例数据。

--create table dbo.report (ticket_number int, status varchar(50), update_date datetime)
--delete from report
--insert into report values (1, 'New', '20160823 13:00')
--insert into report values (1, 'New', '20160823 14:00')
--insert into report values (1, 'Pending', '20160823 16:00')

WITH ticketstatus AS 
(
    SELECT 
        ticket_number,
        ISNULL(status,'null') status,
        update_date,
        ROW_NUMBER() OVER (PARTITION BY ticket_number,status ORDER BY update_date) statusrn  -- will provide us with minimum status rows
        ,ROW_NUMBER() OVER (PARTITION BY ticket_number ORDER BY update_date) ticketrn  -- will tell us which 
    FROM 
        dbo.report
), 
-- get the first row for each status change
minticketstatus AS
(
    SELECT * FROM ticketstatus where statusrn = 1
)
, statuschange as
 (
    SELECT 
        t1.ticket_number
        ,t1.status Status
        ,t1.update_date t1updatedate
        ,t2.status 'StatusChangedTo'
        ,t2.update_date 'StatusChangedDate'
        ,datediff(hh, t1.update_date, t2.update_date) 'time (in hours)'
    FROM minticketstatus t1
    LEFT JOIN minticketstatus t2 ON t2.ticket_number=t1.ticket_number AND t2.status <> t1.status and t2.update_date > t1.update_date
)
SELECT
*   
FROM 
    statuschange