Tina.Lapere Posted February 21, 2018 Share Posted February 21, 2018 Hi All, I'm trying to create a report that will tell me how many calls have been logged outside of our helpdesk hours - so after 5pm but I'm struggling :-( Can anyone help? Thanks in advance Link to comment Share on other sites More sharing options...
Steve Giller Posted February 21, 2018 Share Posted February 21, 2018 I would think that WHERE (DATE_FORMAT(h_datelogged, '%H') > 16 OR DATE_FORMAT(h_datelogged, '%H') < 8) would be along the right lines (assuming you open at 8 and close at 5) Link to comment Share on other sites More sharing options...
Dan Munns Posted February 21, 2018 Share Posted February 21, 2018 I may be wrong but I am assuming that the > 16 means greater than (past) 1600? In which case anything after 5pm would be 17. As we open at 0800 and close at 1800 Monday to Friday but only open 0800 - 1230 on a Saturday would it be as simple as > 12:30? Now I have seen this is may be a good measure for ourselves. Edit: so I am guessing that %H is hour from the date format, so would I have to do '%H' > 12 AND '%M' > 30? Link to comment Share on other sites More sharing options...
Steve Giller Posted February 21, 2018 Share Posted February 21, 2018 We're only taking the hour, > 16 means the hour is greater than 16 so from 17:00 onwards (i.e. 16:59 => 16 and is not included but 17:00 => 17 and is) Counter-intuitive, but it works.%i is the minute, and it would be > 29 for the same reason. Link to comment Share on other sites More sharing options...
Dan Munns Posted February 21, 2018 Share Posted February 21, 2018 Ah I get you. Every day is a school day. Thank you. Time to see what I can create with this now Link to comment Share on other sites More sharing options...
Steve Giller Posted February 21, 2018 Share Posted February 21, 2018 Try: WHERE ((DATE_FORMAT(h_datelogged, '%H') >= 17 OR DATE_FORMAT(h_datelogged, '%H') < 8) AND DATE_FORMAT(h_datelogged, '%w') BETWEEN 1 AND 5) OR (((DATE_FORMAT(h_datelogged, '%H') >= 12 AND DATE_FORMAT(h_datelogged, '%i') >= 30) OR DATE_FORMAT(h_datelogged, '%H') < 8) AND DATE_FORMAT(h_datelogged, '%w') = 6) OR DATE_FORMAT(h_datelogged, '%w') = 0 Where the first line is "Include all calls before 8am or after 5pm Monday to Friday", the second is (hopefully) "Include all calls before 8am or after 12:30pm on Saturday", and the third "Include all calls on Sunday" Should be a reasonable base. Brackets may be an issue! Link to comment Share on other sites More sharing options...
Dan Munns Posted February 21, 2018 Share Posted February 21, 2018 Thanks! Gotta love those brackets lol Link to comment Share on other sites More sharing options...
Tina.Lapere Posted February 21, 2018 Author Share Posted February 21, 2018 @DeadMeatGF - would I have to create this as a measure as my report keeps coming up with an error. I don't know the reporting or analytics part very well. I just want a pretty bar chart somewhere that shows it for Management - and it's proving more challenging than I though :-( Link to comment Share on other sites More sharing options...
Steve Giller Posted February 21, 2018 Share Posted February 21, 2018 @Dan Munns You'll love these even more!!! WHERE ((DATE_FORMAT(h_datelogged, '%H') >= 17 OR DATE_FORMAT(h_datelogged, '%H') < 8) AND DATE_FORMAT(h_datelogged, '%w') BETWEEN 1 AND 5) OR (( (DATE_FORMAT(h_datelogged, '%H') >= 13) OR (DATE_FORMAT(h_datelogged, '%H') >= 12 AND DATE_FORMAT(h_datelogged, '%i') >= 30) OR DATE_FORMAT(h_datelogged, '%H') < 8) AND DATE_FORMAT(h_datelogged, '%w') = 6) OR DATE_FORMAT(h_datelogged, '%w') = 0 The updated second line now says "If the hour is >= 12 AND the minutes are >= 30 OR the hour is >= 13 OR the hour is < 8 on a Saturday" The first attempt actually said "If the hour is >= 12 AND the minutes are >= 30" so it would include 12:30 - 12:59, 13:30 - 13:59, 14:30 - 14:59 etc! @Tina.Lapere I'm not too sure - if no-one else comes in with extra info I'll look again tomorrow. Link to comment Share on other sites More sharing options...
Dan Munns Posted February 21, 2018 Share Posted February 21, 2018 @DeadMeatGF I do believe Hornbill should give you a job! Many thanks. I will have a look at this all in the morning. Link to comment Share on other sites More sharing options...
Tina.Lapere Posted February 23, 2018 Author Share Posted February 23, 2018 Hi, Can anyone show me an image of how I a report for the above looks as I can't get mine to work. Thanks Link to comment Share on other sites More sharing options...
Steve Giller Posted February 23, 2018 Share Posted February 23, 2018 6 minutes ago, Tina.Lapere said: Hi, Can anyone show me an image of how I a report for the above looks as I can't get mine to work. Thanks I've not got an image (yet!) but if you go to Admin->System->Data->Database Direct and run: SELECT h_requesttype AS RequestType, COUNT(*) AS requests FROM h_itsm_requests WHERE (((DATE_FORMAT(h_datelogged, '%H') >= 17 OR DATE_FORMAT(h_datelogged, '%H') < 8) AND DATE_FORMAT(h_datelogged, '%w') BETWEEN 1 AND 5) OR (( (DATE_FORMAT(h_datelogged, '%H') >= 13) OR (DATE_FORMAT(h_datelogged, '%H') >= 12 AND DATE_FORMAT(h_datelogged, '%i') >= 30) OR DATE_FORMAT(h_datelogged, '%H') < 8) AND DATE_FORMAT(h_datelogged, '%w') = 6) OR DATE_FORMAT(h_datelogged, '%w') = 0) AND h_datelogged BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59' GROUP BY h_requesttype You should see the kind of results you'll get. The final line I've added (don't forget the extra brackets!) is just to restrict the results to this year, as I don't know how large your database is! In a report I would expect the date range to be either a user input or automated to produce last week/month etc. For me, the result returns: requesttype | requests ------------------------------------------- Incident | 57 Service Request | 65 Link to comment Share on other sites More sharing options...
Dan Munns Posted February 23, 2018 Share Posted February 23, 2018 @Tina.Lapere you will need to set it up like this (first screenshot) in the filter for the SQL query to work (just remove the leading WHERE from @DeadMeatGFs query) Output should be similar to the sencond screenshot (dependant of selected columns obviously) Link to comment Share on other sites More sharing options...
Steve Giller Posted February 23, 2018 Share Posted February 23, 2018 If you want it as a Measure, it's very similar - I altered the hours to suit my Service Desk, and after resampling I got the results below. Link to comment Share on other sites More sharing options...
Tina.Lapere Posted February 23, 2018 Author Share Posted February 23, 2018 @DeadMeatGF Thank you for your help. Can I ask for a screen shot of the setup of your measure please? Link to comment Share on other sites More sharing options...
Tina.Lapere Posted February 23, 2018 Author Share Posted February 23, 2018 @Dan Munns - I've tried to recreate the report with this: but I get this: However when I run it in the database direct I'm getting 20 results: Link to comment Share on other sites More sharing options...
Steve Giller Posted February 23, 2018 Share Posted February 23, 2018 Something like that Link to comment Share on other sites More sharing options...
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