Page 1 of 1
calculate time between ticket assigned and resolved
Posted: Sun Jun 15, 2014 7:43 am
by grandfso
Hello Klemen,
I notice you got fed up with numerous questions I raise, I'll be stopping doing that really soon
I would like to ask one more question though.
I need to create a query where I can show the time between assigning a ticket to non-default category and marking the ticket as resolved. I took the 'by category' query from reports, but I can't find any useful dates in the DB (e.g. time of assignment and time when ticket was marked as resolved).
thanks and regards, Grand
Re: calculate time between ticket assigned and resolved
Posted: Mon Jun 16, 2014 9:13 am
by Klemen
You have it wrong - I don't mind answering questions (or this forum wouldn't be here), the problem is I can't help with questions that fall into
this category (but different wording)
This is another such question - it would be possible to calculate this time, but it is something that would require substantial changes to the code (and possibly MySQL structure) and is unfortunately not something I can help with, sorry!
Re: calculate time between ticket assigned and resolved
Posted: Mon Jun 16, 2014 12:59 pm
by grandfso
I managed to do something like I needed with SQL. Can you please see if I'm joining the tables right ? I assume that closed ticket has the ticket status = 3, and to find the time of closure I would need to look for last reply to this ticket when the status was changed.
Code: Select all
select
t.trackid,
c.name,
t.dt,
t.subject,
t.status,
lr.dt,
ifnull(date_range_in_seconds(t.dt, lr.dt, '8:00', '16:00'),0)
from
hesk_tickets t
join hesk_categories c ON c.id = t.category
left join (select
max(dt) as 'dt', replyto
from hesk_replies
group by replyto) lr ON lr.replyto = t.id
where lr.dt is not null
Possibly the REAL time between ticket is assigned and resolved could be recorded using custom fields and some PHP ?
p.s. I am newbie with MySQL so please dont mind not elegant code
Re: calculate time between ticket assigned and resolved
Posted: Tue Jun 17, 2014 3:22 pm
by Klemen
Note that ticket closing time does not necessarily match last reply time.
What I would do in your place is add a new column to the "tickets" table called "closed_time" then update this column when a ticket is closed (by customer, by staff (link/status change/reply/bulk in ticket list), automatically for inactivity, ...).
This would be the only reliable way of tracking when a ticket was closed and easy to get from the database (just another column in tickets table).
In fact, I will consider adding this to the next release of HESK as I agree it is a great metric to have and wouldn't complicate things much.
Re: calculate time between ticket assigned and resolved
Posted: Wed Jun 18, 2014 9:45 am
by grandfso
Hi, I want to take easy approach on this and I'm thinking about MySQL Triggers.
Could you validate logic here, the first time the ticked is updated with status 3 is the time this was first closed. Are there other ways hesk uses to close ticket in the DB apart from UPDATE statements ?
E.g.
Code: Select all
CREATE TRIGGER `record_closed_time` BEFORE UPDATE ON `hesk_tickets` FOR EACH ROW
BEGIN
IF new.status=3 AND (old.closed_datetime IS NULL or old.closed_datetime = '') THEN
SET new.closed_datetime = NOW();
END IF;
END$$
DELIMITER ;
thanks and regards, Grand
Re: calculate time between ticket assigned and resolved
Posted: Wed Jun 18, 2014 2:54 pm
by Klemen
It's always set using UPDATE statements, yes.
The best way to make sure is - test it