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

Comments

Please log in to comment

Answers

0
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

Answered 07/28/2014 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity