Jump to content

Recommended Posts

Posted

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

Posted

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
Posted

You can indeed get it into a widget:

 image.png.593b91fe53770b89077e1144fa9e73cb.png

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

  • Like 1
Posted

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? 

Posted

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
Posted

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.

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

Posted

Is that your MS SQL guys which kept throwing MS SQL functions in a query intended for a mySQL database? :P:D  ... (shhh, don't tell them I said this !!! :D

Posted

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

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