Skip to main content

Ticket Backlog with a Custom Timeframe

in Example Reports
Authors list
Published: May 22, 2018|Last updated: Nov 3, 2022

Requirement: a report showing the number of unresolved tickets that are over 14 days old.

It’s simple to use the built-in %PAST_7_DAYS% and %PAST_30_DAYS% placeholders to find tickets that were created over 7 days or 30 days ago, but there isn’t a placeholder for 14 days.

We need a way to compare the date/time of tickets were created with the date/time of 14 days ago.

Solution:

SELECT DPQL_COUNT() AS 'Total unresolved over 14 days old' FROM tickets WHERE tickets.date_created > (NOW() - INTERVAL 14 DAY) AND tickets.status IN ('awaiting_user', 'awaiting_agent')
copy

The WHERE tickets.status IN ('awaiting_user', 'awaiting_agent') clause limits the tickets to those with Awaiting User or Awaiting Agent status, i.e. those which are unresolved.

You might think that we should include On Hold status in the list, but tickets on hold still have a tickets.status of awaiting_agent - hold status is recorded with a separate is.hold value.

HelpfulUnhelpful
next pageTicket Messages per Agent excluding Notes
previous pageReports using Ticket Logs

Please log in or register to submit a comment.