Rule for How long a ticket has been opened
I have made a report to tell me when a Single Select = Y from a ticket and place it into a report. I also have three other fields included, Ticket Created, Ticket Time Open and Ticket Time Closed. I made this using the report wizard. I would like to add a field showing the total time between when the ticket was created and when it was Closed. How can I do this? Any help would be much appreciated. Thank you.
As an example the report would look like as below:
Ticket Created Ticket Time Open Ticket Time Closed Total ticket time Open
11:00 11:02 11:10 :10
As an example the report would look like as below:
Ticket Created Ticket Time Open Ticket Time Closed Total ticket time Open
11:00 11:02 11:10 :10
0 Comments
[ + ] Show comments
Answers (6)
Please log in to answer
Posted by:
GillySpy
13 years ago
The time (in seconds) between when a ticket was created and it was first moved into a closed state is
ref: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff
Note: this only works on 64-bit kboxes
However, you should note that you're making an assumption that is is the total time the ticket was open which may not be true in some cases. For example, depending on how you define open it is possible that a ticket moves directly from stalled to closed and is never actually open. It is also possible that it takes quite a long time between ticket creation and the opening of a ticket. Lastly, what if a ticket is reopened and reclosed?
TIMESTAMPDIFF(SECOND,TIME_CLOSED,CREATED)ref: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff
Note: this only works on 64-bit kboxes
However, you should note that you're making an assumption that is is the total time the ticket was open which may not be true in some cases. For example, depending on how you define open it is possible that a ticket moves directly from stalled to closed and is never actually open. It is also possible that it takes quite a long time between ticket creation and the opening of a ticket. Lastly, what if a ticket is reopened and reclosed?
Posted by:
Lanman145
13 years ago
For these issues there will be no stall and they will not be reopened. I don't have a 64 bit version, I just need a way to tell from the time a ticket was created to the time it was closed.
Posted by:
GillySpy
13 years ago
On a 32-bit version you'll have to go "old school" ;)
UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED)
Posted by:
Lanman145
13 years ago
I apologize for this, here is the SQL statment as I have it now:
SELECT HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED, HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS SLX_Down FROM HD_TICKET WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ( HD_TICKET.CUSTOM_FIELD_VALUE0 LIKE '%Y%') ORDER BY HD_TICKET.CREATED asc
UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED)
My lack of SQL is disturbing, :)
I am getting errors, what am I doing wrong, if I take away the unix time statment I get the correct report without the time. Help PLease.
SELECT HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED, HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS SLX_Down FROM HD_TICKET WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ( HD_TICKET.CUSTOM_FIELD_VALUE0 LIKE '%Y%') ORDER BY HD_TICKET.CREATED asc
UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED)
My lack of SQL is disturbing, :)
I am getting errors, what am I doing wrong, if I take away the unix time statment I get the correct report without the time. Help PLease.
Posted by:
GillySpy
13 years ago
like this:
Also autogenerate the layout again
SELECT HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED, HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS SLX_Down,
UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED) as TOTAL
FROM HD_TICKET WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ( HD_TICKET.CUSTOM_FIELD_VALUE0 LIKE '%Y%') ORDER BY HD_TICKET.CREATED asc
Also autogenerate the layout again
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.