How to Calculate the Count of Working Days Between Two Dates using T-SQL User Defined Function
In Transact-SQL it's a common problem to calculate the number of working days between two dates. SQL developers use user defined functions to calculate count of working days for reusability of t-sql code.
In this t-sql tutorial, I'll start with sql code samples that will help you calculate number of working days between two days excluding holidays. To make it simple for the first T-SQL example, we'll consider only yearly holidays of the working staff.
First start with creating sql table EmployeeHolidayPlanning which stores employees' holiday start and end dates. The first SQL script will also generate sample data to populate SQL database table EmployeeHolidayPlanning.
CREATE TABLE [dbo].[EmployeeHolidayPlanning](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[FromDate] [datetime] NOT NULL,
[ToDate] [datetime] NOT NULL
)
GO
-- 1 day off
INSERT INTO EmployeeHolidayPlanning
SELECT 1, '20090401 00:00', '20090401 23:59'
-- 5 days off
INSERT INTO EmployeeHolidayPlanning
SELECT 1, '20090406 00:00', '20090410 23:59'
-- 3 days non-working days
INSERT INTO EmployeeHolidayPlanning
SELECT 1, '20090428 00:00', '20090430 23:59'
SELECT * FROM EmployeeHolidayPlanning
Here is how sample sql data is seen on holiday planning table where holiday periods of employees are stored
And here is the sample SQL Server user-defined function which can be used to calculate working days between two dates.
Here the employee id is passed to the sql function as a parameter.
Other sql parameters are boundries of the time period; begin date and end date parameters.
Please note that the following dbo.GetWorkingDaysCountForEmployee sample SQL function only considers the holiday table given above.
(
@EmployeeId int,
@DateBegin datetime,
@DateEnd datetime
)
RETURNS int
BEGIN
DECLARE @i int, @cnt int, @Datei datetime
SELECT @cnt = 0, @i = 0
WHILE @i <= DATEDIFF(D, @DateBegin, @DateEnd)
BEGIN
SET @Datei = DATEADD(D, @i, @DateBegin)
IF NOT EXISTS(
SELECT *
FROM EmployeeHolidayPlanning
WHERE
EmployeeId = @EmployeeId AND
@Datei BETWEEN FromDate AND ToDate
)
SELECT @cnt = @cnt + 1
SET @i = @i + 1
END
RETURN @cnt
END
GO
DECLARE @EmployeeId int, @DateBegin datetime, @DateEnd datetime
SELECT @EmployeeId = 1,
@DateBegin = '20090401 00:00', @DateEnd = '20090430 23:59'
SELECT
dbo.GetWorkingDaysCountForEmployee(
@EmployeeId,
@DateBegin,
@DateEnd
) AS Working_Days_Count
/*
CREATE TABLE EmployeeWorkingDays
(
EmployeeId int,
Monday bit,
Tuesday bit,
Wednesday bit,
Thursday bit,
Friday bit,
Saturday bit,
Sunday bit
)
GO
INSERT INTO EmployeeWorkingDays SELECT 1, 1,1,1,1,1, 0,0
*/
GO
ALTER FUNCTION GetWorkingDaysCountForEmployee
(
@EmployeeId int,
@DateBegin datetime,
@DateEnd datetime
)
RETURNS int
BEGIN
DECLARE @i int, @cnt int
DECLARE @Monday int, @Tuesday int, @Wednesday int,
@Thursday int, @Friday int, @Saturday int, @Sunday int
SELECT
@Monday = ISNULL(Monday, 0),
@Tuesday = ISNULL(Tuesday, 0),
@Wednesday = ISNULL(Wednesday, 0),
@Thursday = ISNULL(Thursday, 0),
@Friday = ISNULL(Friday, 0),
@Saturday = ISNULL(Saturday, 0),
@Sunday = ISNULL(Sunday, 0)
FROM EmployeeWorkingDays (NoLock)
WHERE EmployeeId = @EmployeeId
SET @cnt = 0
SET @i = 0
DECLARE @Datei datetime
DECLARE @wd int
WHILE @i <= DATEDIFF(D, @DateBegin, @DateEnd)
BEGIN
SET @Datei = DATEADD(D, @i, @DateBegin)
SELECT
@wd = CASE (DATEPART(dw, @Datei) + @@DATEFIRST) % 7
WHEN 1 THEN ISNULL(@Sunday, 0)
WHEN 2 THEN ISNULL(@Monday, 0)
WHEN 3 THEN ISNULL(@Tuesday, 0)
WHEN 4 THEN ISNULL(@Wednesday, 0)
WHEN 5 THEN ISNULL(@Thursday, 0)
WHEN 6 THEN ISNULL(@Friday, 0)
WHEN 0 THEN ISNULL(@Saturday, 0)
END
IF @wd = 1
IF NOT EXISTS (
SELECT *
FROM EmployeeHolidayPlanning
WHERE
EmployeeId = @EmployeeId AND
@Datei BETWEEN FromDate AND ToDate
)
SELECT @cnt = @cnt + @wd
SET @i = @i + 1
END
RETURN @cnt
END
GO
DECLARE @EmployeeId int, @DateBegin datetime, @DateEnd datetime
SELECT @EmployeeId = 1,
@DateBegin = '20090401 00:00', @DateEnd = '20090430 23:59'
SELECT
dbo.GetWorkingDaysCountForEmployee(
@EmployeeId,
@DateBegin,
@DateEnd
) AS Working_Days_Count