This content has been marked as final. Show 7 replies
I am looking for the same requirement... Query all SRs that are between 2.5 to 4 hours from creation time, between working hours 8-5, excluding weekends.
No issues with the calculation, I will help you out on this.
Just wanted to confirm that does the weekdays calculation include public holidays also. I mean when we calculate the SLA, do we need to calculate if the weekday is a holiday or not?
Hi Paul, can you post your solutions, I have the same requirement to calculate the working time, only Monday to Friday, 8:00am to 5:00pm are calcuated. thanks!!
The best solution would be to have a custom component to calculate the SLA and populate the value in the field. Pull up the value in the field.
Let me know if this is acceptable or you still want to populate using reports.
I will make a couple of assumptions for building the expression here:
Imagine your working hours in a day are fixed i.e. 7 AM to 4 PM(if this gets changed, you will have to modify the formula accordingly unless we can think of something to automate it) and you will have fields like Activity Start time and Activity End time. Also you wont have any activity started on a weekend or a public holiday.
then at max there can be 2 scenarios:
1) Activity that is completed on the same day
2) activity that is completed on some other day(like next day or day after)
so my Fx would be something like this:-
CASE WHEN DAY(Activity Start Time) = DAY(Activity End Time) THEN TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) ELSE TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) - (60*15*(DAY(Activity End Time) - DAY(Activity Start Time))) END
The above formula will take care of weekends and public holidays. 15 here represents the hours from 4 PM to 7 AM on next day and since we are calculating minutes, its multiplied by 60.
How does the formula ELSE TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) - (60*15*(DAY(Activity End Time) - DAY(Activity Start Time))) END
take care of the holidays and weekends?
How would the function TIMESTAMPDIFF identify the holidays and weekends.
Hey Paul, thanks for the prompt. I see now that the formula is incomplete.
As I have just taken the difference in days between the Activity Start Time and End Time, it will consider any weekend. But its incomplete as it considers only 15 hours of a day and not the remaining 9 hours for weekends. For holidays - we do need a field or something to identify it like Activity Type = 'holiday'.
So the ELSE* part of revised formula would be something like this:
... ELSE TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) - (60*15*(DAY(Activity End Time) - DAY(Activity Start Time))) - (60*9*(COUNT(DISTINCT CASE WHEN DAYOFWEEEK(Date.Date) IN (6,7) OR ~some field to identify holiday~ = 'something' THEN 1 END)) END
now imagine there's one activity that started on Friday 7:00 AM and ends on coming Monday at 4:00 PM.
Actual duration is 18 hours.
Now,the first term after ELSE will give 81*60 minutes. next term should give 45*60 minutes. third term would be 18*60 min.
so the entire expression would return 18 hours.
Even if there's a holiday, then i guess the 'OR' part should capture it.
I hope this makes sense. Please let me know if it should need any further corrections.