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 fortickets_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
 copy

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.

By adding GROUP BY tickets_logs.ticket_id, you get a more informative output: