Report: Service Desk ticket by time of day?
Is there a way to report on tickets submitted during a certain window of time in a day without doing SQL? If SQL is required, does anyone have a cheat sheet I could use?
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
naturboy1975
12 years ago
Here is some simple MySQL to get you started:
Select
extract(day from `CREATED`) day,
extract(hour from `CREATED`) hour,
count(*)
from
`ORG1`.`HD_TICKET`
where month(created) = 7
group by extract(day from`CREATED`), extract(hour from`CREATED`);
It uses two functions to help you:
Extract allows a part of the timestamp field to be returned. Further documentation of the parts of the timestamp value that can be extracted are posted to: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
The other function, month() is only one example of the same type of function that truncates the timestamp into portions that can be grouped. I selected all tickets that were created in July (note that the year is NOT selected), and grouped the count of tickets by the day and hour of the day in which they were created.
Output looks like:
'day','hour','count'
'1', '3', '2'
'1', '6', '1'
'1', '7', '1'
'1', '9', '7'
Select
extract(day from `CREATED`) day,
extract(hour from `CREATED`) hour,
count(*)
from
`ORG1`.`HD_TICKET`
where month(created) = 7
group by extract(day from`CREATED`), extract(hour from`CREATED`);
It uses two functions to help you:
Extract allows a part of the timestamp field to be returned. Further documentation of the parts of the timestamp value that can be extracted are posted to: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
The other function, month() is only one example of the same type of function that truncates the timestamp into portions that can be grouped. I selected all tickets that were created in July (note that the year is NOT selected), and grouped the count of tickets by the day and hour of the day in which they were created.
Output looks like:
'day','hour','count'
'1', '3', '2'
'1', '6', '1'
'1', '7', '1'
'1', '9', '7'
Posted by:
KevinG
12 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
so that the conversation will remain readable.