calculate time between ticket assigned and resolved

[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

[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!

[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

[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.

[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

[Klemen]

It's always set using UPDATE statements, yes.

The best way to make sure is - test it