I would like to know how many tickets were solved at a certain time.
For example:
Agent X worked two hours on Saturday
Agent Y worked 8 hours on Sunday
How many tickets were closed directly and tickets that were set to pending autoclose on that day.
In the OTRS itself I find no possibilities to indicate times.
Right now I have the problem that my SQL query also ejects tickets where the agent had already set the ticket to autoclose a few days before. For example, I'm issued an agent who didn't work on the weekend but closed 50 tickets. (autopending tickets from the week before)
But I only need the values for the day itself.
Code: Select all
SELECT CONCAT( first_name, ' ', last_name ) Name, u.login user, COUNT( t.id ) AS '# of tickets closed'
FROM ticket t
LEFT JOIN users u ON u.id = t.user_id
AND u.valid_id =1
LEFT JOIN queue q ON q.id = t.queue_id
LEFT JOIN ticket_state ON ( ticket_state.id = t.ticket_state_id ) WHERE (ticket_state.id = '2' or ticket_state.id = '3' or ticket_state.id = '7' or ticket_state.id = '8')
AND t.change_time between '2018-03-16 18:00:00' AND '2018-03-19 05:00:00'
GROUP BY u.id