Would it be possible to add a function that calculates total ticket open time with Work hours only (output should be seconds)?
Currently we can calculate total ticket open time in hours with the following function:
Select = (UNIX_TIMESTAMP(tickets.date_resolved) - UNIX_TIMESTAMP(tickets.date_created)) / (60 * 60)) AS 'total ticket open time in all statuses (hr)'
We also have a function to calculate total user waiting time with this function. tickets.total_user_waiting, and a function to calculate user wait time in work hours only, tickets.total_user_waiting_wh.
If we would have a total ticket open time work hours function, we could build reports which would be able to show how much time it spent with the user and with our agent by subtracting tickets.total_user_waiting_wh form tickets.total_open_time_wh. I can only give a user a total open time and a total sitting with user time, which does not really reflect how many hours a ticket has been open with us as it counts out of office hour and weekends, which skews the calculation and times quite a bit.
Would this DPQL function in the ticket table possible to build?