Jump to content

Widget - No. requests logged throughout day


dwalby

Recommended Posts

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

Link to comment
Share on other sites

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)

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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.

image.png

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)

image.png

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 :)  

 

 

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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......

Link to comment
Share on other sites

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) :) 

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...