Requirement: for each agent, list the subjects of all tickets they resolved last month in the ‘Mergers’ department, with a clickable link to view each ticket.

When you output the ticket ID, it is automatically made into a link to the ticket (and the links are kept if you export a PDF), so we just have to SELECT tickets.id as well as tickets.subject.

To limit the query to tickets in a particular department, we have to specify the department by its ID. The easiest way to find that out is to use View Options at the top left of the admin interface, enable showing IDs, then look at Tickets > Departments. In the solution below we’ve assumed the ID for Mergers is 4.

Instead of just a list of tickets, we want to output a list for each agent, so we use SPLIT BY.

The completed query is:

SELECT tickets.subject, tickets.id
FROM tickets
WHERE tickets.department = '4' AND tickets.date_resolved = %LAST_MONTH%
AND tickets.status IN ('resolved', 'archived')
SPLIT BY tickets.agent
 copy

Note that we’ve used tickets.status IN ('resolved', 'archived') to include tickets that have been archived after being resolved. (Archiving is a performance feature useful for helpdesks with very large numbers of tickets; it is not enabled by default).