Scripting

This is a bit of overkill, but it's one of our operational reports.  It lists technician, number of tickets open for more than 14 days, number of tickets closed in the last 7 days, number of ticket closed in the last 28 days, number of tickets closed in the last 56 days, assigned open tickets created in the last 7 days, and total open tickets.  These counts are regardless of queue.  You can adjust the dates and selection criteria to meet your specific needs.

SELECT 
    USER.USER_NAME AS Technician,
    (SELECT 
            COUNT(a.ID)
        FROM
            HD_TICKET a
                LEFT JOIN
            HD_STATUS b ON a.HD_STATUS_ID = b.ID
        WHERE
            b.STATE <> 'closed'
                AND a.MODIFIED > DATE_SUB(NOW(), INTERVAL 14 DAY)
                AND a.OWNER_ID = USER.ID) AS 'Open_over_14_days',
    (SELECT 
            COUNT(c.ID)
        FROM
            HD_TICKET c
                LEFT JOIN
            HD_STATUS d ON c.HD_STATUS_ID = d.ID
        WHERE
            d.STATE = 'closed'
                AND c.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
                AND c.OWNER_ID = USER.ID) AS 'Closed_last_7_days',
    (SELECT 
            COUNT(e.ID)
        FROM
            HD_TICKET e
                LEFT JOIN
            HD_STATUS f ON e.HD_STATUS_ID = f.ID
        WHERE
            f.STATE = 'closed'
                AND e.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 28 DAY)
                AND e.OWNER_ID = USER.ID) AS 'Closed_last_28_days',
    (SELECT 
            COUNT(g.ID)
        FROM
            HD_TICKET g
                LEFT JOIN
            HD_STATUS h ON g.HD_STATUS_ID = h.ID
        WHERE
            h.STATE = 'closed'
                AND g.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 56 DAY)
                AND g.OWNER_ID = USER.ID) AS 'Closed_last_56_days',
    (SELECT 
            COUNT(i.ID)
        FROM
            HD_TICKET i
                LEFT JOIN
            HD_STATUS j ON i.HD_STATUS_ID = j.ID
        WHERE
            j.STATE <> 'closed'
                AND i.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
                AND i.OWNER_ID = USER.ID) AS 'Created_last_7_days',
    (SELECT 
            COUNT(k.ID)
        FROM
            HD_TICKET k
                LEFT JOIN
            HD_STATUS l ON k.HD_STATUS_ID = l.ID
        WHERE
            l.STATE <> 'closed'
                AND k.OWNER_ID = USER.ID) AS 'Total_open_tickets'
FROM
    HD_TICKET
        LEFT JOIN
    USER ON HD_TICKET.OWNER_ID = USER.ID
GROUP BY USER.USER_NAME
ORDER BY USER.USER_NAME;