SQL query to find the duration of a status from each ticket

LeoJ

Hello to everyone,

I want to extract all Tickets with a certain status into an Excel Sheet. Important is the time, how long the status was set in the ticket. I have the Databank and allready found the tables with all the statuses "os_ticket_status" and all the tickets "os_ticket".
I wanted to built an query but my knowledge in SQL isnt the best. Maybe someone can help me.

I started with this little query.

select id, name FROM ost_ticket_status WHERE name = Kundenaktion INNER JOIN ost_ticket ON ost_ticket_status.id = ost_ticket.status_id

but this query would only show the last Status.

Is it even possible to show the time between tow statuses ?

OS Ticket: v1.10 (901e5ea) / Patch-8
PHP : 5.6.31
Server: Windows Server 2012 R2 Standard
We are using a MariaDB Version 10.2 and I work with HeidiSQL Version. 9.5.0.5196 (64bit)

Thanks.

ntozier

You would have to do an additional join on the ost_thread_event ... but I'm not sure how you would query that for the two related changes but you use the timestamp on each to determine the time between them. I've never really looks at that table before but I imagine that you would look up all the thread entries for a given ticket you would take the ticket id and
SELECT id FROM ost_thread WHERE object_id='TID';
where TID would be the ticket id #.

Then you could use the thread id to query against ost_thread_entry like
SELECT * FROM ost_thread_entry WHERE thread_id='984';
and that would give you all the thread updates.

ntozier

side note the second query I think should actually be
SELECT * FROM ost_thread_event WHERE thread_id='984' AND data LIKE '%status%';

LeoJ

This is my solution for now. I think i export the data and make the rest with excel and VBA.

SELECT ost_thread_event.thread_id, ost_thread_event.data, ost_thread_event.timestamp, ost_thread.id, ost_thread.object_id, ost_ticket.ticket_id, ost_ticket.number
FROM (ost_thread_event INNER JOIN ost_thread ON ost_thread_event.thread_id =ost_thread.id)
INNER JOIN ost_ticket ON (ost_thread.object_id = ost_ticket.ticket_id) WHERE data LIKE '%status%';

I take the "thread_id" from the table "ost_thread_event" and compare it to the id in "ost_thread". Then i take the "object_id" from "ost_thread" and compare it to the "ticket_id" in "ost_ticket". I add the Incident "number" and the data, so when i sort it now by "number" i get the status and the timestamp of each incident.

Thanks for the tip. It helped me a lot. ?

ntozier

Very welcome. ?

So it sounds like I should close this thread as you got things all sorted. Please feel free to start a new thread if you have another question, etc.