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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
The time (in seconds) between when a ticket was created and it was first moved into a closed state is
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?
Answered 02/01/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 02/01/2011 by: Lanman145
Blue Belt

Please log in to comment
0
On a 32-bit version you'll have to go "old school" ;)

UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED)
Answered 02/01/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 02/01/2011 by: Lanman145
Blue Belt

Please log in to comment
0
like this:

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
Answered 02/01/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
AHHHH Thank you good Sir. I am very grateful for this.
Answered 02/01/2011 by: Lanman145
Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity