ERWIN & BUSINESS ANALYTICS

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 *

nineteen − seventeen =

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

0 Comments

Submit a Comment

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

5 × two =

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

My First Blog Post SQLSatHolland 790

Yeah, my first blog is LIVEAfter a good talk with Reza Rad from RADACAD during SQLSatHolland, I decided to start my first blog!Knowledge sharing is very important, it gives me a lot of energy. But it also gives others people in the community energies to pick up new...

ADF: Get Metadata Activity stopped working

Meta Data ActivityToday my pipelines in Azure Data Factory (ADF) suddenly stopped working. The output structure was not found. Quit strange while these pipelines have been running for weeks.    Invalid Template After debugging my Pipeline I found out the...

How to check your SQL Server Quota in Azure?

Azure Subscription Usages for SQL Server  Last week we reached our Logical server Quota in Azure. By default you're only allowed to add 20 Logical Servers, but we wanted to have some more for testing purposes.Microsoft Support You can submit a support ticket trough...

LEAP 2018

Lead Enterprise Architect ProgramFrom the 4th of December until the 6th of December I've joined the LEAP (Lead Enterprise Architect Program) 2018 Data/AI from Microsoft NL. Three  days on the Microsoft Campus in Redmond joining business and technical sessions from a...

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...

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...

Azure Data Factory: Save a pipeline as a Template

Saving a PipelineAnother great improvement in Azure Data Factory. Saving you template!How can you save your template? First of all you need to connect your Azure Data Factory to  a GIT integration. Both Azure DevOps GIT and GitHub are supported. Please follow this...

New Microsoft Azure Certifications

Microsoft Certification by Solution Area Handy overview of the new Microsoft Azure Certifications. More details can be found here Feel free to leave a comment

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 Purview MSIgnite Spring 2021 Announcements

Azure Purview Ignite 2021 AnnoucementPricing This week the Azure Purview announced that they will extend the Azure Purview offer to provision 4 Capacity Units of the Data Map for free till May 31, 2021! Charging will start on June 1, 2021. Great news for customer who...