Report showing number of tickets that a user has that are over 3 days old taking into account a 5 day work week.
My manager found this query on these forums and hopefully the author or someone else knowledgeable can assist. This gets the tickets that are over 3 days old and displays them. Now they would like to have a report that shows users, and how many tickets they have that are over 3 days old taking into account the weekends etc.
Is there something easy I can add to this query or do I need to bark up a different tree?
SELECT T.ID AS 'TICK', T.TITLE AS 'Problem Description', U.FULL_NAME AS 'Owner', T.CREATED AS 'Time Created', CONCAT(DATEDIFF(NOW(), T.CREATED),'D') AS 'Time Opened', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_PRIORITY P ON (P.ID = T.HD_PRIORITY_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='IT')
WHERE NOW() > DATE_ADD(T.CREATED, INTERVAL 5 DAY) AND T.OWNER_ID <> 0 AND S.STATE = 'opened'
ORDER BY T.CREATED