im getting an error in this script for my project. Im using MariaDB with HeidiSQL v12.2. I dont know what its happen with this script but the error occur at line 85. Sorry for my English.
CREATE DEFINER=`root`@`%` PROCEDURE `SP_KPI_TIEMPO_SOLUCION`(
in pinicio datetime
,in pfin datetime
)
BEGIN
select
sum(contador) as Total_Tickets
,sum(cumple) as Total_Cumple_SLA
,round(sum(cumple) / sum(contador) * 100) Kpi
/*,(
select color from colorkpi
where
sum(cumple) / sum(contador)*100 >= rango1 and sum(cumple) / sum(contador)*100<rango2
) as Color*/
from
(
select
t.tn as ticket
,t.sla_id as id_tipo_sla
,s.name as nombre_sla
,1 as contador
,t.create_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
/and tt.state_id in (2,3,10,13,14)/
and (e.type_id=3
or tt.state_id=14)) as cierre
,
case
when t.ticket_state_id=13 then 1 /Cierre Técnico se considera como 1 cumple/
else
case
when
s.solution_time>total_hh(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
/and tt.state_id in (2,3,10,13,14)/
and (e.type_id=3
or tt.state_id=14) -- se agrega el esatdo solucionado como cerrado
), "%Y,%m,%d %H,%i")) then 1
else 0 end
end
as Cumple
,s.solution_time
,total_hh(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
/and tt.state_id in (2,3,10,13,14)/
and e.type_id=3
), "%Y,%m,%d %H,%i")) tiemporeal
from ticket t
,sla s
,ticket_state te
where
t.create_time between /'2018/04/01 00:00'/pinicio and /'2018/04/30 23:59'/pfin + interval 1439 minute
and t.type_id in (1,8)/8: Requerimiento 1:Indicente/
and t.sla_id=s.id
and t.queue_id in (8) /Solo mesa de ayuda/
/and t.ticket_state_id in (2,3,10,13,14,9) /Estado Cerrados Solamente/
and t.ticket_state_id=te.id
and te.type_id in (3,7) / 3, cerrados - 7, fusionados*/
and te.valid_id=1
order by ticket
)
a;
END
Hope someone can help me, Thanks!
