Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

NetWorkDays Function - SQL Server

RSS
Modified on Wed, Oct 22, 2008, 8:21 PM by Administrator Categorized as Microsoft Office, SQL Server
This function reproduces the Excel function NETWORKDAYS().

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