|
|
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
Free Exam Vouchers
|