Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

NetWorkDays Function - SQL Server

/*--------------------------------------------------------------------------------
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