SQL批处理数据，希望根据上次更新时间戳找到每个状态(分钟)的运行时间

Question

票证可以在各种状态之间来回跳动，需要确保在票证的整个生命周期中以分钟为单位捕捉票证在每种状态下的时间。

我对每个状态都做了下面的语句，但是由于票在状态中弹跳，所以没有得到正确的时间

SELECT t.ticket_number, 
MIN(update_date) start_time, MAX(update_date) stop_time,
DATEDIFF(second, min(update_date),max(update_date)) elapsed_sec 
FROM xxx.dbo.report t 
WHERE t.status= 'StopTime' 
GROUP BY t.ticket_number;

状态

关闭,完成,新的,Waiting-1,Waiting-2,Waiting-3,StopTime,Research-1,Research-2,解决,特殊要求,打开请求,空

这对我来说是全新的批处理数据。我正在寻找的帮助是一种以分钟为单位计算的方法，票在每个状态下的时间，以及最后的运行总数(如果可能的话)。

样本数据:

'ticket_number  status  update_count    update_date
    156097  Stoptime    5               1/14/13 21:34
    164481  Stoptime    5               2/9/13 21:36
    164826  Stoptime    5               2/11/13 21:34
    165931  Stoptime    5               2/17/13 21:36
    177348  Stoptime    5               3/3/13 21:34
    179232  Stoptime    5               3/12/13 22:34
    181079  Stoptime    5               3/23/13 22:34
    258181  Stoptime    1               6/25/15 9:50
    257336  Stoptime    9               7/17/15 2:28
    245035  Closed      84              7/17/15 2:32
    259573  Closed      642             7/20/15 2:22
    245476  Closed      116             7/22/15 3:18
    251601  Closed      3               8/4/15 4:18
    251601  Closed      3               8/4/15 4:18
    259732  Closed      22              8/12/15 9:42
    264957  Closed      29              8/22/15 19:53'

期望输出样本:*票号是票号的不同计数，而度量标准的格式是(mm:ss)谢谢!声誉不足以发布图像，因此无法满足预期输出的所有状态。我的歉意。

Ticket  Closed  Complet New   Total Running Time (mm:ss)
156097  00:00   00:00   00:00   00:00
164481  00:00   00:00   00:00   00:00
164826  00:00   00:00   00:00   00:00
165931  00:00   00:00   00:00   00:00
177348  00:00   00:00   00:00   00:00
179232  00:00   00:00   00:00   00:00
181079  00:00   00:00   00:00   00:00
245035  00:00   00:00   00:00   00:00
245476  00:00   00:00   00:00   00:00

Answer 1

尝试这个查询，它计算每个状态的实时时间并构建一个报告

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 2

查看使用PIVOT/UNPIVOT的文档。您可以在状态上添加分组，并使用枢轴将AVG(calculated_minutes)值推出列。此外，如果您熟悉SSRS，那么使用列分组将是一个简单的报告。

所以你不想使用PIVOT。然后，您必须手动使用平均值或票号的总和来完成此操作。我在这张票里输入了这个，不能保证它会运行，这是你正在搜索的一般概念。

SELECT
    ticket_number,  
    SumStatus1=SUM(SumStatus1),
    SumStatus2=SUM(SumStatus2),
    SumStatus3=SUM(SumStatus3),
    SumAll=SUM(SumaAll)
FROM
(
    SELECT
        ticket_number,
        SumStatus1=CASE WHEN Status='status1' THEN elased_sec ELSE NULL END,
        SumStatus2=CASE WHEN Status='status2' THEN elased_sec ELSE NULL END,
        SumStatus3=CASE WHEN Status='status3' THEN elased_sec ELSE NULL EDN,
        SumaAll=elapsed_sec
    FROM
    (
        SELECT
            ticket_number,
            status,
            DATEDIFF(second, min(update_date),max(update_date)) elapsed_sec 
        FROM 
            report t
        GROUP BY 
            t.ticket_number,
            t.status    
    )AS A
)AS B
GROUP BY
    ticket_number