This is always a performance thing, ETL-wise, but also in stored procedures.
I’ve published an example on how to calculate the number of working days (rounded) via a scalar-valued function:
Function: Calculate working days
This example however calculates the working minutes between dates, while taking into account the company and/or national holidays and weekends.
You could extend the functionality to also include local office hours.
1) The number of working minutes between a given date/time-value and the present (NOW)
2) The number of working minutes between two given date/time-values
3) The number of working minutes between two given date/time-values from a large dataset
I ran a test on a large dataset which included actual order dates and after the so called ‘WorkingMinutes’-table was made in 4~5 seconds, it took only 3 seconds to process more than 1 million records!
Download the SQL script here: