How many open and closed tickets per agent per date

[xaviercmx]

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

[xaviercmx]

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

[mtrissi]

Did someone find an answer to this?

I'm trying to do the same thing.