I need to calculate the "time to restore / actual working hours" for a ticket based on numerous conditions:
Business Hours - each week day may have them different. Everything that is not within business hours should fall into "off clock" time.
Holidays - they should be counted entirely as "off clock".
Status "Pending" or "Resolved" - whenever the ticket goes into one of those, the clock should be stopped for the duration of the status.
Obviously the following conditions may overlap at times, so the only way I could think of for solving this problem was to search for "Creation", "Pending Start", "Pending End" and "Restoration" log entries and then split the days between each log entry into dummy log entries of type "Ongoing". Then I could put it all into a table and create help columns like "is_business_day", "is_pending", "is_holiday", "shift_start", "shift_end", "is_before_business_hours", "is_past_business_hours" etc with values of 0 or 1 per each log entry. This way even if two or more of the above conditions overlap (say a holiday and Pending status, they will be counted as one "off clock" time for as long as they overlap.
I hope this makes sense...
If you can think of a more straightforward solution then by all means please let me know 🙂
... View more