What do you need as a minimum.

A Date Dimension that includes the day of the week and a fact in which you want to calculate the number of workdays between 2 dates.

First of all we need to create a table or view where we define our Holidays and Closing Days.

In my case I just created a simple view where I’ve defined my Holidays, there are more ways to achieve this functionality, but this works as well.

After we’ve created our Holidays we need to create the the following Function:

Finally include this function in your Query.

If we want to know how many workingdays there are between OrderDate 2019-05-29 and ExpectedDeliverydate 2019-06-18 we call the function dbo.fnBusinessday(OrderDate , ExpectedDeliverydate ), the result will be 12.

This is because we have defined 2019-05-30 and 2019-06-10 as Holidays and in between we have 3 weekends.

If there are no holidays then only the weekend days are not included.

With the above method you can use the calculation dynamically every time.

If there are any questions, please leave them in the comments below.

The sample code can be found here.

Feel free to leave a comment