Calculate Workingdays including Holidays with T-SQL
Erwin
Calculate Workingdays between 2 Date columns
Recently I have been getting some questions from my customers, can I calculate the number of workdays between 2 dates? Of course my answer was, yes you can. But I do want certain closing dates and holidays of our company not to be included either. That became a little bit more difficult. Eventually I was able to solve this and would like to share that with you.
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.
