Panthersfball234 Panthersfball234 - 3 months ago 8
SQL Question

SQL Batch Data, Hope to find running time for each Status (minutes) based on Last Update Timestamps?

Tickets can bounce in and out of statuses and need to be sure I capture the time in minutes that the ticket is in each status through the life cycle of the tickets.

I did the statement below for each status, but will not get the proper time because of tickets bouncing in and out of statuses.

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;


Statuses

Closed,
Complete,
New,
Waiting-1,
Waiting-2,
Waiting-3,
StopTime,
Research-1,
Research-2,
Resolved,
Special Request,
Opened Request,
Null


This is completely new to me working with batch data.. What I'm looking for help with is a way to count in minutes, how long the ticket is in each status, as well as a running total at the end (if possible).

Sample Data:

'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'


Sample of Expected Output: *Ticket number is a distinct count of ticket number while the format of the metrics are in (mm:ss) Thank you! Reputation not up enough to post images, so could not fit all statuses on the expected output. My apologies.

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

try this query, it counts real time in each status and builds a report

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
Comments