[rt-users] Reports based on duration between open and update/closed

[Allen]

> Is there a way to get reports on how much time a ticket has been open?

I know this is not the sanctioned way to do things because it is not
using RT APIs, but it works for now:

# How many days have tickets been open:
SELECT id, EffectiveId, Created, Resolved, WEEK(Created) as week, Status,
               TO_DAYS(IF(Resolved != '0000-00-00 00:00:00' AND
Resolved != '1970-01-01 00:00:00',
                          DATE(Resolved), CURDATE())) -
TO_DAYS(DATE(Created)) as days_open
        FROM Tickets WHERE Queue = 5
             AND Type = 'ticket'
             AND
             (
                 Status IN ('open','stalled','new','autoclose')
                 OR
                 (
                     Status = 'resolved'
                     AND Resolved > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                 )
             )
        ORDER BY id


# how many resolved per month
$sql = "SELECT COUNT(id) as ct, LPAD(MONTH(Resolved), 2, '0') as mon,
YEAR(Resolved) as yr
        FROM Tickets
        WHERE Queue = 5
              AND Status = 'resolved'
              AND Resolved > DATE_SUB(CURDATE(), INTERVAL 180 DAY)
              AND Type = 'ticket'
        GROUP BY yr,mon";

# how many created per month
$sql = "SELECT COUNT(id) as ct, LPAD(MONTH(Created), 2, '0') as mon,
YEAR(Created) as yr
        FROM Tickets
        WHERE Queue = 5
            AND Status NOT IN ('rejected', 'deleted')
            AND Created > DATE_SUB(CURDATE(), INTERVAL 180 DAY)
            AND Type = 'ticket'
        GROUP BY yr,mon";


# how many resolved per day, per queue
$sql = "SELECT COUNT(T.id) as ct, Q.Name as QueueName,
LPAD(DAYOFYEAR(T.Resolved), 3, '0') as dayresolved, YEAR(T.Resolved)
as year
        FROM Tickets T
        LEFT JOIN Queues Q on Q.id = T.Queue
        WHERE T.Status = 'resolved'
            AND T.Resolved > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
            AND T.Type = 'ticket'
        GROUP BY dayresolved, T.Queue";

# how many created per day, per queue
$sql = "SELECT COUNT(T.id) as ct, Q.Name as QueueName,
LPAD(DAYOFYEAR(T.Created), 3, '0') as daycreated, YEAR(T.Created) as
year
        FROM Tickets T
        LEFT JOIN Queues Q on Q.id = T.Queue
        WHERE T.Created > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
            AND T.Status NOT IN ('rejected', 'deleted')
            AND T.Type = 'ticket'
        GROUP BY daycreated, T.Queue";

# status for unresolved tickets
$sql = "SELECT COUNT(T.id) as ct, T.Status
        FROM Tickets T
        LEFT JOIN Queues Q on Q.id = T.Queue
        WHERE Status NOT IN ('deleted', 'rejected', 'resolved')
            AND T.Type = 'ticket'
        GROUP BY T.Status";