Calculating length of time a Ticket was suspended

I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").

The data is stored something similar to the following...

Ticket Date Status
-
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"

This is the result i'd like to see....

Ticket Start Hold End Hold Time Suspended
- -- --
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...

Has anybody done something along the same lines? If so, could you point me in the right direction?

Thanks in anticipation

Jon

Hello Jon,

This will give you the TicketID and the Start and End Date for Hold status. From your report, you can get the time difference for 'Time Suspended' (or you could do the datediff directly in this query).

select tl.TicketID,
(select min(date) from TicketLog tl1 where TicketID = tl.TicketID and tl1.Status = 'To On Hold') as StartHold,
(select max(date) from TicketLog tl2 where TicketID = tl.TicketID and tl2.Status = 'From On Hold') as EndHold,
from TicketLog tl

Here is another way, the min and max date are returned for any log that has 'On Hold' in the status. So, if it is required that each ticket can be on hold only once and you're reporting tickets that are no longer on hold, this would work.

select tl.TicketID, min(tl.date), max(tl.date)
from TicketLog tl
where tl.Status like '% on Hold'
group by tl.TicketID
having count(*) > 1

Hope this helps.

Jarret

|||

Hi Jarret

Thanks for your input.

I was able to get the same results as the ones your code generates... but unfortunately it's not quite what I need.

The above code generates only one row for each ticket with the first date a ticket went into suspend status and the last date a ticket left suspend status. A Ticket can enter and leave suspend status many times (or never) during it's life. I need to see a row for each time a ticket entered and left suspend status. I can then do a datediff and sum up all the times in suspend status. Then I can take this time off the total length of time the ticket has been open.

Cheers

Jon

|||

I have now got a view with data something like the following...

Ticket Clock HoldStart HoldStop

331 Start 2007-02-01 10:00:00 NULL
331 Stop NULL 2007-02-01 11:00:00
332 Start 2007-02-15 12:01:00 NULL
332 Stop NULL 2007-02-15 13:45:11
333 Start 2007-02-17 10:00:06 NULL
333 Stop NULL 2007-02-17 11:07:00
333 Start 2007-02-18 11:11:12 NULL
333 Stop NULL 2007-02-18 16:01:00
333 Start 2007-02-18 18:07:00 NULL
333 Stop NULL 2007-02-19 17:00:00
334 Start 2007-02-20 10:00:00 NULL
334 Stop NULL 2007-02-20 11:00:00
334 Start 2007-02-20 14:00:00 NULL
334 Stop NULL 2007-02-21 17:00:00

A little bit closer, but still not what I need... from this, I'd to see...

Ticket Clock HoldStart HoldStop

331 Start 2007-02-01 10:00:00 2007-02-01 11:00:00
332 Start 2007-02-15 12:01:00 2007-02-15 13:45:11
333 Start 2007-02-17 10:00:06 2007-02-17 11:07:00
333 Start 2007-02-18 11:11:12 2007-02-18 16:01:00
333 Start 2007-02-18 18:07:00 2007-02-19 17:00:00
334 Start 2007-02-20 10:00:00 2007-02-20 11:00:00
334 Start 2007-02-20 14:00:00 2007-02-21 17:00:00

any ideas?

|||

I think you are going to need to loop through your dataset with a cursor (or similar) to get the values you're looking for.

Jarret