Jump to content

Reports - Calls logged outside of office hours


Tina.Lapere
 Share

Recommended Posts

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

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

@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

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

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
 Share

×
×
  • Create New...