Hi guys!
I'm trying to get some statistics in SQL query, how many open and closed tickets per agent per date. I have searched the forum for some query in sql, but I have not been lucky, however I have no idea how to solve it .
For example
O = Open
C = Closed
Output
+--------+-------------------+
______ |06-20-17|06-19-17|...
+--------+-------------------+
| Agent | O | C | O | C |...
+--------+-------------------+
| Marco | 9 | 4 | 8 | 3 |...
| Stefan| 4 | 2 | 2 | 1 |...
| Marco | 5 | 5 | 6 | 4 |...
+--------+-------------------+
Could someone with knowledge in SQL query and database tables help me?
Thanks
How many open and closed tickets per agent per date
Moderator: crythias
Re: How many open and closed tickets per agent per date
I have been working on this query but the result of the number of tickets per user is incorrect. Can you help me find the error?
Code: Select all
SELECT CONCAT( first_name, ' ', last_name ) Name, u.login user, COUNT( t.id ) AS '# of tickets'
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
AND t.ticket_state_id =4
AND t.create_time >= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )
GROUP BY u.id
Re: How many open and closed tickets per agent per date
Did someone find an answer to this?
I'm trying to do the same thing.
I'm trying to do the same thing.