SQL produce a MySQL report

Question

SQL produce a MySQL report

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good morning,

In the following table is an extract from the otrs.ticket_history table where it depicts a log of the status changes of a ticket / request:

Thegoalistogetatablelike:

in which the sum of the time in minutes (I used the TIMESTAMPDIFF function) has been grouped in each state.

Example: In ticket 50383, 2658 minutes elapsed between 2/27/2017 6:31 p.m. (id 917593) and 3/1/2017 2:50 p.m. (id 918771).

And so on to other states. (if it is the last state of the ticket the difference must be calculated between the beginning of the last state and the current moment, for example function now ().

Is it possible to produce such a table with SQL only? I only have read permissions on this database.

Thank you.

sql

asked by anonymous 02.03.2017 / 17:23

2 answers

Default MVC - What layer classes of files do they belong to? GraphViewData is not recognized

score 0 · Answer 1

--maxixos e minimos
select ticket_id , 
       state_id , 
       min(create_time) tmin, 
       max(create_time) tmax
from  ticket_history
group by ticket_id , state_id 

--pivot deste sql
select ticket_id ,
       max(case when state_id = 1 then (tmax-tmin) else null end) state_id_1,
       max(case when state_id = 2 then (tmax-tmin) else null end) state_id_2,
       max(case when state_id = 3 then (tmax-tmin) else null end) state_id_3,
       ...
       max(case when state_id = "n" then (tmax-tmin) else null end) state_id_"n"

from
(  
select ticket_id , 
       state_id , 
       min(create_time) tmin, 
       max(create_time) tmax
from  ticket_history
group by ticket_id , state_id
) ticket_id_max
group by ticket_id

The bad thing is that the number of columns is fixed, new status the sql needs to be redone.

score 0 · Answer 2

Dear Motta,

When you execute the "maximum minimum" for ticket 50447, you get:

Whatiswronginformation,forexamplestate12.Bythelogitisnoticedthattheticketpasses2timesbystate12,inwhichthefirstofthetimeswillhavebeenlessthanoneminute(inthesameminutepassedagaintothestate1)andthesecondtimeitstarted3/2/201712:45PMtothecurrenttime.

Bytheinformationextractedbythequerythatyouproducedwewouldbeledtocalculatethatstate12beganin"tmin" and it remained there until "tmax", which is not true.

I appreciate the help anyway.