Custom Report writing
Hi,
Trying to create a report that will list the following
Show number of tickets logged each hour by day
Show number of tickets logged each hour by week
Show number of tickets logged each hour by month
Can anyone please help
Thanks
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
grayematter
9 years ago
The query below will give you a data cube. You can open the output file in Excel or other spread sheet. Then use the pivot table or pivot chart functionality to get the data sliced how you like. For more info on SQL date and time functions, check out http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format.
SELECT
date_format(HD_TICKET.CREATED, '%Y') as Created_Year,
date_format(HD_TICKET.CREATED, '%m') as Created_Month,
week(HD_TICKET.CREATED) as Created_Week,
date_format(HD_TICKET.CREATED, '%H') as Created_Hour,
HD_QUEUE.NAME as 'Queue',
HD_CATEGORY.NAME as 'Category',
count(*) as Ticket_Count
FROM
HD_TICKET
LEFT JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN
HD_QUEUE ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where
HD_TICKET.CREATED > date_sub(now(), interval 6 MONTH)
and HD_TICKET.HD_QUEUE_ID <> 2
group by Created_Year , Created_Month , Created_Week , Created_Hour , HD_QUEUE.NAME , HD_CATEGORY.NAME
order by Created_Year , Created_Month , Created_Week , Created_Hour , HD_QUEUE.NAME , HD_CATEGORY.NAME
