SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NetWorkDays


NetWorkDays

Author
Message
garylzon
garylzon
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 1
Has any one created a function/stored procedure that will calculate NetWorkDays like the excel networkdays function?

From Excel Help file:
---------------------

NETWORKDAYS

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

How?

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

End_date is a date that represents the end date.

Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates. Learn about array constants. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.



MarkusB
MarkusB
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10015 Visits: 4208
Though I'm sure there are plenty of other scripts out there check out this article
http://www.sqlservercentral.com/columnists/achigrik/udfexamples.asp

It contains several datetime related functions incl. one to calculate workdays.

Markus Bohse
davidcriley
davidcriley
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 54
Hi,

I trawled the net and found a version by Patrick Jasinski, who I must give the credit too.
It did't do exactly what I wanted in that it did not handle minus dates so I have altered it to allow for this.

To answer your question this worked for me.

CREATE function [dbo].[NetWorkDays](
@StartDate datetime
,@EndDate datetime
) returns int as begin

declare
@result int
,@StartDate2 datetime
,@EndDate2 datetime
,@DateSwap1 datetime
,@DateSwap2 datetime
,@ReturnNegative BIT

set @DateSwap1 = @StartDate
set @Dateswap2 = @EndDate

SET @ReturnNegative = 0

IF @EndDate < @StartDate
BEGIN
SET @ReturnNegative = 1
SET @StartDate = @Dateswap2
SET @EndDate = @DateSwap1
END


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

if @ReturnNegative = 1
BEGIN
SET @result = @result * -1
END

return @result

end

GO
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search