|
|
How to Calculate the Count of Working Days Between Two Dates using T-SQL User Defined Function
/*
CREATE TABLE [dbo].[EmployeeHolidayPlanning](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[FromDate] [datetime] NOT NULL,
[ToDate] [datetime] NOT NULL
)
GO
INSERT INTO EmployeeHolidayPlanning
SELECT 1, '20090401 00:00', '20090401 23:59'
INSERT INTO EmployeeHolidayPlanning
SELECT 1, '20090406 00:00', '20090410 23:59'
INSERT INTO EmployeeHolidayPlanning
SELECT 1, '20090428 00:00', '20090430 23:59'
*/
CREATE FUNCTION GetWorkingDaysCountForEmployee
(
@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
|
Related SQL Resources
SQL Server Articles
SQL Server Tools
SQL Blog
SQL Server 2008 Blog
Certification Exams Blog
Reporting Services Blog
Analysis Services Blog
MS SQL Server Forums
|