Calculate Workingdays including Holidays with T-SQL

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.

T-SQL

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.

WeekendDay

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.

Holidays

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

Businessdays

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.

WorkingDays

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

WorkingDaysNoHolidays

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

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

five × five =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Goodbye 2020 Hello 2021

Goodbye 2020 Started to work for InSpark Last year was certainly an eventful year. Started with a new job at InSpark and after 10 weeks we all know what happened, the first intelligent lockdown. The Netherlands was partially locked, but our office was immediately...

Updated competency exams and certifications Data Platform and Data Analytics for 2020

Retiring and new exams and certifications as of June 30 2020 A lot of Exams and certifications for Data Platform and Data Analytics are retiring on June 30 2020. All retired Exams and certifications will remain eligible for competency attainment and renewal until June...

Azure Synapse Analytics Code Repository has arrived

Azure Synapse Analytics Code repository‎I just opened my Azure Synapse Analytics Workspace and got a great surprise, the option Git Configuration is available as of today‎.    After a long wait, today the Git Configuration option became available in Azure Synapse...

Azure Purview Public Preview Starts billing

Billing for Azure Purview(Public Preview)As of January 20th 2021 0:00 UTC Azure Purview will starts billing.Preview From January 20 ,2021 Azure Purview will start billing. During the Public Preview, you will only be billed if you exceed the 4 capacity units for Azure...

Restore a accidentally deleted Azure SQL Database

Help I deleted my Logical Server​OOPS Have you ever experienced that you accidentally deleted your Logical Server in Azure?  Because, for example, you made your Pipeline wrong. Surely. And of course you didn't have a backup in your storage either.Well I must confess...

Proud to be an ADF Hackathon winner!

Azure Data Factory HackathonAround the clock During the event 'Around the clock Azure SQL and Azure Data Factory' event on Feb 3, 2021, they kicked-off the Azure Data Factory (ADF) Hackathon. Recording of this event can be found here.Winner I submitted a ADF Pipeline...

Azure Data Factory: New functionalities and features

New functionalities and featuresLast week, a number of great new functionalities and features were added within Azure Data Factory. I would like to take you in some details in the blog below:Customer key With this new functionality you can add extra security to your...

Yeah My website is Finally Live

Yeah, my first built website is LIVEThe last couple of weeks I've been working hard to design my website and finally the moment is there. My website live and I'm really proud.  Building my own website The last couple of weeks/months I've been working hard... to...

Enable Pattern Rules in Azure Purview

How can I enable Pattern Rules?​Pattern Rules Last night I was preparing for a demo with Azure Purview. As always, I walk through all the activity hubs to see if there are any new options. This time I noticed that the Pattern Rules option was greyed out. Resource Set...

Calculate the Last Day of the Month using SQL

Calculate the Last DayToday I needed to calculate the last day of the previous month for a Customer.Ever heard from the function EOMONTH? Searching on the web I came across a function I never heard from before EOMONTH, this function can be used as of SQL Server 2012....