CountBusinessDays

Working with Access, I needed to calculate business days between dates, excluding holidays, and with an eventual conversion fo the DB to SQL Server, rewrote the VBA into an SQL function, below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

— =============================================
— Author: James Igoe
— Create date: 2008-08-29
— Description: Calculates business days between 2 dates
— =============================================
ALTER FUNCTION [dbo].[Fx_WorkdayCount]
(
@sStartDate As DATETIME,
@sEndDate As DATETIME
)
RETURNS SMALLINT
AS
BEGIN
— Declare the return variable here
DECLARE @BusinessDays SMALLINT

DECLARE @intDaysTotal As SMALLINT
DECLARE @intDaysWeeks As SMALLINT
DECLARE @intRemainder As SMALLINT
DECLARE @intRemainderPost As SMALLINT
DECLARE @intRemainderPre As SMALLINT

SET @intDaysTotal = DateDiff(day, @sEndDate, @sStartDate)
SET @intDaysWeeks = ((@intDaysTotal / 7) * 5)
SET @intRemainder = @intDaysTotal % 7

IF @intRemainderPre = 1
SET @intRemainder = @intRemainder - 1
ELSE IF @intRemainderPre = 7
SET @intRemainder = @intRemainder - 2

IF @intRemainderPost = 1
SET @intRemainder = @intRemainder - 1
ELSE IF @intRemainderPost = 7
SET @intRemainder = @intRemainder - 2

IF (@intRemainderPost < @intRemainderPre) And @intRemainderPre <> 7
SET @intRemainder = @intRemainder - 2

SET @BusinessDays = @intDaysWeeks + @intRemainder

— Return the result of the function
RETURN @BusinessDays

END

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License