K1000 - Report showing each closed ticket DATE Closed (dd/mm/yyyy) and time it took from opened to closed
I need some help in creating a simple report that will show the last 12 months closed tickets, I only need the following two columns:
Date (dd/mm/yyyy) the ticket was closed
Time each ticket took to close in days or hours.
I appreciate your help
That's all only 2 columns.
1 Comment
[ + ] Show comment
-
Thank you JasonEgg. I'll try that and will post the outcome. Cheers - aoh 7 years ago
Answers (1)
Please log in to answer
Posted by:
JasonEgg
7 years ago
How about this:
select HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.MODIFIED AS `TIME CLOSED`,
TIMEDIFF(HD_TICKET.MODIFIED,HD_TICKET.CREATED) AS `TIME TO CLOSE`
from HD_TICKET join HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
where DATE_SUB(NOW(), INTERVAL 1 YEAR) > HD_TICKET.MODIFIED
and HD_STATUS.STATE = 'closed'
Comments:
-
change the fourth line to the following to show time as days (rounded):
DATEDIFF(HD_TICKET.MODIFIED,HD_TICKET.CREATED) AS `DAYS TO CLOSE` - JasonEgg 7 years ago
