There is no built-in functionality for calculating business hours. The duration calculations, like Time To Resolve, are done in the data model, which means that it does a SQL expression to do the calculation (determining the difference in time between two dates). A few customers have created a User Defined Function in their database to take in two date values and then calculate the duration based on their business hours. This is usually most easily accomplished if only one time zone and one work shift is involved.
So, while there is no built-in functionality, it IS possible to do.
Perfect.Thank you much. I'll work with the team to see if they'd like to attempt something like this.
Thanks Gregg. This is exactly what I am trying to achieve. I created my UDF in the Xtraction database and ensured my Xtraction user has correct rights. I changed the datamodel, adding a column using the UDF. When trying to use my new column, I am getting an error. I also tried using direct SQL statement within Xtraction, and am getting an error he UDF cannot be found (screenshot attached).
Can you help pointing out the step(s) I am missing?
I am assuming that you are NOT reporting against the Xtraction database, but rather some other database. If this assumption is correct, then your problem is that you added the UDF to the wrong database (or, at least, are not calling the UDF correctly).
Ideally, for best performance, you place the UDF in the same database where the data is. If you are reporting on, say, LANDESK Service Desk, and create the UDF in the Service Desk database, the expression in the datamodel would be something like "dbo.AddWeekdays(incident.open_date,incident.close_date)".
If, for some reason, you can't add the UDF to that database and, instead, add it into the Xtraction database, you can still use it. Assuming the Service Desk database and the Xtraction database are both on the same server, the data model expression would be "xtraction.dbo.AddWeekdays(incident.open_date,incident.close_date). This is because the query that is going to run in the Service Desk database is now referencing a function located in a different database (xtraction)". If the Xtraction database is on a different server, then you have to link the two SQL servers and change the expression to something like ""xtr_server.xtraction.dbo.AddWeekdays(incident.open_date,incident.close_date)", where xtr_server is the name of the linked server where the Xtraction database resides.
But, by far, the easiest thing is to just locate the UDF in the same database where the query is being run. Easiest setup. Easiest data model expression. Best performance.
Just remember to grant Execute permissions on the UDF to whatever account Xtraction is using to access the database.