dwalby Posted March 9, 2018 Posted March 9, 2018 Hi All, I'm looking to create a widget/report that shows the average number of requests logged throughout a day (each hour, between 8am and 6pm) so we can see our busiest times, etc. Any suggestions on how to achieve this? Thanks in advance
Steve Giller Posted March 9, 2018 Posted March 9, 2018 You can get this kind of data from: SELECT COUNT(*) AS num_calls, CASE WHEN DATE_FORMAT(h_datelogged, '%H') < 8 THEN '00' WHEN DATE_FORMAT(h_datelogged, '%H') > 17 THEN '00' ELSE DATE_FORMAT(h_datelogged, '%H') END AS logged_hour FROM h_itsm_requests GROUP BY logged_hour ORDER BY logged_hour but I'm not sure if you could get that into a Widget or not. (00 denotes logged outside of working hours) 1 1
Dan Munns Posted March 9, 2018 Posted March 9, 2018 You can indeed get it into a widget: But I am assuming that there needs to be a CURDATE filter (?) as it look like it is showing all calls ever logged at the hours stated. (Stealing this widget if we can get it working @dwalby ) 1
dwalby Posted March 9, 2018 Author Posted March 9, 2018 @Dan Munns Yeah I'd feel bad for you if you had 1116 requests logged at 8am
Dan Munns Posted March 9, 2018 Posted March 9, 2018 Stealing and modifying @DeadMeatGF query to add calls logged for the week: SELECT COUNT(*) AS 'Number of Calls', CASE WHEN DATE_FORMAT(h_datelogged, '%H') < 8 THEN '00' WHEN DATE_FORMAT(h_datelogged, '%H') > 17 THEN '00' ELSE DATE_FORMAT(h_datelogged, '%H') END AS logged_hour FROM h_itsm_requests WHERE (h_datelogged >= (CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY) ) GROUP BY logged_hour ORDER BY logged_hour To only show the current day just change: (h_datelogged >= (CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY) ) to (h_datelogged >= CURRENT_DATE) Just a quick one though, I changed num_calls to 'Number of calls' and that works but if I change logged_hour to anything it fails. Any ideas why?
Steve Giller Posted March 9, 2018 Posted March 9, 2018 Assuming you changed it in all three places, it might be that in order to GROUP is cannot have spaces in the name?
Dan Munns Posted March 9, 2018 Posted March 9, 2018 Yeah I changed it in all places. Just groups the 00 calls... i thought sticking it in quotes would work. Oh well.
Steven Boardman Posted March 9, 2018 Posted March 9, 2018 You seem to have what you need here, but if you wanted to see some other examples there is a dashboard in the sandbox with some hopefully useful widgets. The Tickets raised per hour widget is using a measure and then the Tick Type as shown below which allows you to change this up for hourly, daily etc. Using this example you can also use the Sample Period filter to look at this data over different periods (this year, this month etc) Current credentials for the sandbox are here: admin.hornbill.com/demo user: grahamc password: H0rnbill But it looks like you have this all in hand anyway 1
Steve Giller Posted March 9, 2018 Posted March 9, 2018 I think it's the way the engine parses the GROUP BY when it's grouping on a derived field, if you can change it to "Hour" without issue then that's almost certainly it - when it parses the GROUP BY fieldname the quotes won't be there so it'll see the whitespace and expect that to be the next command.
Dan Munns Posted March 9, 2018 Posted March 9, 2018 Just now, DeadMeatGF said: I think it's the way the engine parses the GROUP BY when it's grouping on a derived field, if you can change it to "Hour" without issue then that's almost certainly it - when it parses the GROUP BY fieldname the quotes won't be there so it'll see the whitespace and expect that to be the next command. Thanks. I changed it to Hour and it works fine. Seems If I add quotes to Hour it break it again so you are correct (not that I expected any different to be fair) I wish I knew SQL......
Steve Giller Posted March 9, 2018 Posted March 9, 2018 2 minutes ago, Dan Munns said: I wish I knew SQL...... So do I - I rely on guesswork and instinct
Dan Munns Posted March 9, 2018 Posted March 9, 2018 5 minutes ago, DeadMeatGF said: So do I - I rely on guesswork and instinct And I rely mainly on other people....
Victor Posted March 9, 2018 Posted March 9, 2018 Is that your MS SQL guys which kept throwing MS SQL functions in a query intended for a mySQL database? ... (shhh, don't tell them I said this !!! )
Dan Munns Posted March 9, 2018 Posted March 9, 2018 To be fair to them my questions usually go "How do I do <this random thing> in SQL?" They do ask and I don't tell so probably my own fault. Besides, now I have you on speed dial @Victor (or @ dial anyway) for all my mySQL needs. (oh and "other people" tends to mean anyone I can steal erm "borrow" SQL queries from)
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now