/*-------------------------------------------------------------------------------- Credits: Patrick Jasinski, 02/27/08 Purpose: This function reproduces the Excel function NETWORKDAYS(), which is available via the Excel Analysis Add-In. Algorithm (1) Calculate the number of days between the First Sunday AFTER StartDate and the First Sunday BEFORE EndDate. At 7 days per week, convert this to weeks; then, at 5 weekdays per week, convert this to weekdays. (2) Add in the weekdays between the Start Date and the following Sunday. (3) Add in the weekdays between the EndDate and the previous Sunday. --------------------------------------------------------------------------------*/ create function [dbo].[NetWorkDays]( @StartDate datetime ,@EndDate datetime ) returns int as begin declare @result int ,@StartDate2 datetime ,@EndDate2 datetime set @StartDate2 = dateadd(d,8-datepart(dw, @StartDate), @StartDate) set @EndDate2 = dateadd(d,1-datepart(dw ,@EndDate), @EndDate) set @result = datediff(d, @StartDate2, @EndDate2) * 5 / 7 + datediff(d, @StartDate, @StartDate2) - 1 + datediff(d, @EndDate2, @EndDate) - case when datepart(dw,@StartDate) = 1 then 1 else 0 end - case when datepart(dw,@EndDate) = 7 then 1 else 0 end return @result end
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.