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*/