SQL Server, T-SQL, ASP.NET, Javascript, SAP, ABAP Programming

Kodyaz Development Resources

Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.
Welcome to Kodyaz Development Resources Sign in | Join | Help




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














Copyright © 2004 - 2010 Eralper Yilmaz. All rights reserved.
Powered by Community Server, by Telligent Systems