How to Dynamically Calculate Working Days Between Two Dates in SQL Server

Learn how to adjust your SQL Server queries to dynamically account for weekends and holidays when calculating working days between dates.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Calculate number of working days between two dates

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Dynamically Calculate Working Days Between Two Dates in SQL Server

When working with SQL Server, it's common to encounter scenarios where you need to analyze records based on specific date ranges. One frequent challenge is calculating the number of working days between two dates, especially when the start or end dates coincide with weekends or holidays. For businesses that operate with different counts of working days, this can lead to discrepancies in data analysis and reporting. In this blog, we will explore a method to dynamically calculate the working days between two dates considering weekends and holidays.

The Problem

Consider the scenario where you have an SQL Server query that runs daily at noon. This query looks for records entered between the previous day and the current date, which works perfectly fine from Tuesday to Saturday. However, issues arise when the report runs on Mondays or the day after a holiday. In the example provided, Saturdays are classified as working days, which complicates the calculations when the report date changes.

Example Query

Here’s a basic representation of the query you're currently using:

[[See Video to Reveal this Text or Code Snippet]]

While this works well from Tuesday to Saturday, the increment for DATEADD needs to adjust dynamically for Sundays or holidays.

The Solution

To solve this problem, we need to modify the logic used in our SQL query so that we can check the calendar table for the last working day before the given report date.

Step 1: Understand the Calendar Table

Before implementing the solution, it's crucial to ensure that you have a calendar table that contains two essential fields:

IsHoliday: A binary indicator (0 or 1) that signifies whether a specific date is a holiday.

IsWorkingDay: A binary indicator (0 or 1) that specifies whether a date is considered a working day.

Your calendar table might look somewhat like this:

CalDateIsHolidayIsWorkingDay2022-06-18012022-06-19002022-06-20102022-06-2101Step 2: Modify the Query

With the understanding of your calendar table, you can now write a query that dynamically finds the most recent working day before your @ ReportDate. Here's how to incorporate that into your query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

Subquery: The subquery retrieves the maximum CalDate that is less than the specified @ ReportDate where that date is not a holiday and is considered a working day.

Main Query: This retrieves records from SampleTable created between the computed last working day and the current date.

Conclusion

By implementing the above method, you effectively overcome the challenge of dynamically calculating working days while accounting for weekends and holidays. This enhancement ensures that your reporting system delivers accurate insights regardless of when the report is executed. By leveraging the calendar table, you can maintain the integrity of your data analyses, ensuring that your queries run smoothly every time.

Feel free to adopt this approach and adjust it according to your specific business needs, ensuring accurate calculations and informed decisions based on reliable data.