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.