Solution Time per Ticket

Moderator: crythias

Post Reply
eduardrilo1
Znuny newbie
Posts: 2
Joined: 19 Jan 2023, 21:45
Znuny Version: OTRS 5.5
Real Name: Eduardo Estay
Company: GENESYS

Solution Time per Ticket

Post by eduardrilo1 »

I'm trying to run a query to calculate the time to solve some tickets in otrs database. My company gave me the query done (it runs correctly), however, it brings me only blank columns. I have been reviewing the query and all the tables that it refers to have data and I cannot understand why it does not bring data. What this query does is calculate the time from when a ticket is created until it is solved, however, the user can pause the response time and start it again I hope you can help me.

I expect to receive data in the columns. I have a table called "Data" in which it has been filled with generic dates every 1 minute that helps me to calculate the Total_hh_nueva function, however, I don't understand how it is related to the query to calculate the time.

Code: Select all

select 
 t.tn as ticket,
 t.sla_id as id_tipo_sla,
 s.name as nombre_sla,
 1 as contador,
 t.create_time,
 t.change_time,
 (select min(tt.create_time) from ticket_history tt,
 ticket_state e
where tt.ticket_id=t.id 
and tt.article_id is not null
and tt.state_id=e.id
and tt.state_id<>1
) fecha_primer_cambio,
case
when 
30>total_hh_nueva(DATE_FORMAT(t.create_time, "%Y,%m,%d %H,%i"), DATE_FORMAT((select min(tt.create_time) from ticket_history tt
,ticket_state e
where tt.ticket_id=t.id 
and tt.article_id is not null
and tt.state_id=e.id
and tt.state_id<>1), "%Y,%m,%d %H,%i")) then 1
else 0 end as Cumple,
30 as sla,
total_hh_nueva(DATE_FORMAT(t.create_time, "%Y,%m,%d %H,%i"), DATE_FORMAT((select min(tt.create_time) from ticket_history tt,
ticket_state e
where tt.ticket_id=t.id 
and tt.article_id is not null
and tt.state_id=e.id
and tt.state_id<>1), "%Y,%m,%d %H,%i")) tiemporeal,
u.login usuario
from ticket t,
sla s,
users u
where
t.create_time BETWEEN "2022/01/01"
AND "2022/12/31"  + interval 1439 minute 
and t.type_id in (1,8) /*8: Requerimiento 1:Indicente*/
and t.sla_id=s.id
and t.user_id=u.id
and t.queue_id in (8) /*Solo mesa de ayuda*/
/*and t.ticket_state_id in (2,3,10,13,14) /*Estado Cerrados Solamente*/
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Solution Time per Ticket

Post by root »

Hi,

Why not use a report?

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
eduardrilo1
Znuny newbie
Posts: 2
Joined: 19 Jan 2023, 21:45
Znuny Version: OTRS 5.5
Real Name: Eduardo Estay
Company: GENESYS

Re: Solution Time per Ticket

Post by eduardrilo1 »

Thanks for your answer but I don't understand very well. I need the data to export it to power bi. I'm working in HeidiSQL.
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Solution Time per Ticket

Post by root »

Hi,

Create a ticket list with the builtin report functionality and use the created CSV with PowerBI.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
skullz
Znuny superhero
Posts: 618
Joined: 24 Feb 2012, 03:58
Znuny Version: LTS and Features
Real Name: Mo Azfar
Location: Kuala Lumpur, MY
Contact:

Re: Solution Time per Ticket

Post by skullz »

i kind of wrote these few years back while running 6.0..try it yourself either it fit your needs..

good luck

Code: Select all

SELECT CONCAT_WS(' ', u.first_name,u.last_name) AS OWNER_NAME,
t.tn AS OTRS_TICKET_NUMBER,
t.title AS TITLE,
q.name AS 'QUEUE',
t.create_time AS OPEN_TIME,
CASE WHEN th.history_type_id IN ('1','27') AND th.state_id IN ('2','3') THEN MAX(th.create_time) ELSE NULL END AS CLOSE_TIME,
CONCAT(
TIMESTAMPDIFF(day,t.create_time,CASE WHEN th.history_type_id IN ('1','27') AND th.state_id IN ('2','3') THEN MAX(th.create_time) ELSE NULL END) , ' days ',
MOD( TIMESTAMPDIFF(hour,t.create_time,CASE WHEN th.history_type_id IN ('1','27') AND th.state_id IN ('2','3') THEN MAX(th.create_time) ELSE NULL END), 24), ' hours ',
MOD( TIMESTAMPDIFF(minute,t.create_time,CASE WHEN th.history_type_id IN ('1','27') AND th.state_id IN ('2','3') THEN MAX(th.create_time) ELSE NULL END), 60), ' minutes '
) AS TimeDiff,
CASE WHEN th2.history_type_id IN ('8') THEN MIN(th2.create_time) ELSE NULL END AS AGENT_RESPONSE_TIME,
tt.name AS 'TYPE',
s.name AS 'SERVICE',
sla.name AS 'SLA',
ts.name AS 'STATE',
t.customer_id AS CUSTOMER_ID,
t.customer_user_id AS ALIAS
FROM ticket t
LEFT JOIN ticket_state ts ON (t.ticket_state_id=ts.id)
LEFT JOIN queue q ON (t.queue_id=q.id)
LEFT JOIN ticket_type tt ON (tt.id=t.type_id)
LEFT JOIN service s ON (t.service_id=s.id)
LEFT JOIN sla ON (t.sla_id=sla.id)
LEFT JOIN users u ON (t.user_id=u.id)
LEFT JOIN ticket_history th ON (t.id=th.ticket_id AND th.history_type_id IN ('1','27') AND th.state_id IN ('2','3'))
LEFT JOIN ticket_history th2 ON (t.id=th2.ticket_id AND th2.history_type_id IN ('8'))
LEFT JOIN customer_user cu ON (t.customer_user_id=cu.login)
WHERE ( CAST(t.create_time AS DATE) BETWEEN '2020-01-01' AND '2022-12-31')
GROUP BY t.tn
ORDER BY Owner_Name
Post Reply