Requirement: create a report which counts the number of times any ticket created this month was moved into the Support department (ID 3).
To find ticket actions that changed the department, we look for
tickets_logs.action_type = 'changed_department'.
For each of these actions, the
tickets_logs.id_after value tells us the department the ticket moved to. So to find actions that moved a ticket into the Support department, we can use
tickets_logs.id_after = 3.
The finished query is:
SELECT DPQL_COUNT() AS 'Times a ticket moved into Support department' FROM tickets_logs WHERE tickets_logs.action_type = 'changed_department' AND tickets_logs.date_created = %THIS_MONTH% AND tickets_logs.id_after = 3
This counts the total times any ticket was moved into the Support department; if the count you get back is 400, there’s no way to tell if that’s 400 tickets being moved once, or the same ticket being moved 400 times.
GROUP BY tickets_logs.ticket_id, you get a more informative output: