January 29, 2009 at 3:41 pm
This may not even be possible but I have been staring at this data all day trying to figure out how to determine the time spent by each support group with the following data I get as a history feed from this ticketing system. The comments tell the story of what actually happen with each row.
create table dbo.ticket_history (
RNID varchar(8),
RouteQ varchar(6),
SupportGroup varchar(15),
AssignedTech varchar(10),
time_stamp datetime,
comment varchar(120)
);
insert into dbo.ticket_history (RNID, RouteQ, SupportGroup, AssignedTech, time_stamp, comment)
select 'T1234567','OPEN','FIRSTLEVEL','','1/28/2009 16:50','Ticket was created by help desk and assigned to FIRSTLEVEL support' union all
select 'T1234567','OPEN','SECONDLEVEL','USER1','1/28/2009 16:57','USER1 who works in FIRSTLEVEL support assigned himself to ticket, rerouted to SECONDLEVEL support and saved the record' union all
select 'T1234567','CLOSED','SECONDLEVEL','USER2','1/28/2009 17:03','USER2 assigned himself to ticket and closed the ticket';
select * from ticket_history order by time_stamp;
RNID RouteQ SupportGroup AssignedTech time_stamp comment
-------- ------ --------------- ------------ ----------------------- ------------------------------------------------------------------------------------------------------------------------
T1234567 OPEN FIRSTLEVEL 2009-01-28 16:50:00.000 Ticket was created by help desk and assigned to FIRSTLEVEL support
T1234567 OPEN SECONDLEVEL USER1 2009-01-28 16:57:00.000 USER1 who works in FIRSTLEVEL support assigned himself to ticket, rerouted to SECONDLEVEL support and saved the record
T1234567 CLOSED SECONDLEVEL USER2 2009-01-28 17:03:00.000 USER2 assigned himself to ticket and closed the ticket
I tried the following query however for the first support group I'm not sure how to capture the time spent since there isn't an ending entry (CLOSED) like there is for the second support group. The first support group's end time actually needs to be the time_stamp where it was saved when routed to the second support group.
SELECT RNID, SupportGroup, MIN(time_stamp) AS StartTime, MAX(time_stamp) AS EndTime
FROM ticket_history
GROUP BY RNID, SupportGroup
RNID SupportGroup StartTime EndTime
-------- --------------- ----------------------- -----------------------
T1234567 FIRSTLEVEL 2009-01-28 16:50:00.000 2009-01-28 16:50:00.000
T1234567 SECONDLEVEL 2009-01-28 16:57:00.000 2009-01-28 17:03:00.000
I'm all spent trying to figure this out. Any ideas?
drop table dbo.ticket_history;
January 29, 2009 at 5:01 pm
Try this query, which uses the ROW_NUMBER() function to sequentially number rows belonging to the same ticket (RNID) ordered by the time_stamp column. This table expression is then joined with itself on the derived row number column (rn) so that consecutive pairs of records are joined.
;WITH cteEvent (RNID, SupportGroup, time_stamp, rn) AS
(
SELECT RNID, SupportGroup, time_stamp, rn = ROW_NUMBER() OVER (PARTITION BY RNID ORDER BY time_stamp)
FROM ticket_history
)
SELECT E1.RNID, E1.SupportGroup, ElapsedTimeInMinutes = SUM(DATEDIFF(minute, E1.time_stamp, E2.time_stamp))
FROM cteEvent E1
INNER JOIN cteEvent E2 ON (E1.RNID = E2.RNID AND E1.rn + 1 = E2.rn)
GROUP BY E1.RNID, E1.SupportGroup
January 29, 2009 at 5:55 pm
I don't think you have all the data in the table that you need.
You can't just use the start time of the next task to determine the end time; some task has to be last so there won't be a next task start time to look at for that task.
January 30, 2009 at 3:16 am
If the last event in the ticket_history table for a particular RNID records when the ticket is closed (RouteQ = 'CLOSED') and that is an end state for the ticket processing, then I think there is enough data to calculate the elapsed times by SupportGroup using the query in my previous post.
If the ticket is not closed, then a modification would be required to include the elapsed time from the latest event in the ticket_history table to the current system time. The following modified query does this, though there's probably a more efficient method of achieving the same thing.
;WITH cteEvent (RNID, SupportGroup, time_stamp, rn) AS
(
SELECT T.RNID, T.SupportGroup, T.time_stamp, rn = ROW_NUMBER() OVER (PARTITION BY T.RNID ORDER BY T.time_stamp)
FROM (
SELECT RNID, SupportGroup, time_stamp FROM ticket_history
UNION ALL
SELECT T1.RNID, T1.SupportGroup, GETDATE()
FROM ticket_history T1
INNER JOIN (
SELECT RNID, time_stamp = MAX(time_stamp)
FROM ticket_history
GROUP BY RNID
) T2 ON (T1.RNID = T2.RNID AND T1.time_stamp = T2.time_stamp)
WHERE (T1.RouteQ <> 'CLOSED')
) T
)
SELECT E1.RNID, E1.SupportGroup, ElapsedTimeInMinutes = SUM(DATEDIFF(minute, E1.time_stamp, E2.time_stamp))
FROM cteEvent E1
INNER JOIN cteEvent E2 ON (E1.RNID = E2.RNID AND E1.rn + 1 = E2.rn)
GROUP BY E1.RNID, E1.SupportGroup
ORDER BY E1.RNID, E1.SupportGroup
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply