Query to calculate networkdays

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to calculate networkdays

Author

Topic

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 01:07:29

Hi

I have a requirement where i need to calculate the age of a work order excluding the Weekends (Sat,Sun) in an SQL table, this i need to updated as a formula for a particulay column in the SQl table so when a task startdate is enterred and submitted the Age field gets populated with the number of working days.
Im not sure how to go about this.
Appreciate any help with this

.

Example:
startdate = '04/09/2010'
currentdate = '04/12/2010'
the result should show 1 day and not 3 days.

Thanks

Ewan Gilby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 01:18:49

use a calendar table if you have one.

If not use F_TABLE_DATE


select	count(*)
from	F_TABLE_DATE('20100409', '20100412')
where	WEEKDAY_NAME	not in ('Sat', 'Sun')

KH
[spoiler]Time is always against us[/spoiler]

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 02:11:59

hi khtan

when i run:
select count(*)
from F_TABLE_DATE('20100409', '20100412')
where WEEKDAY_NAME not in ('Sat', 'Sun')

i get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'F_TABLE_DATE'.

Ewan Gilby

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-15 : 02:12:20

Much Simple function as your requirement

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN

declare @i int
Declare @count int
declare @diff int
set @diff=datediff(d,@date1,@date2)
set @i=0
set @count=0
While(@i<@diff)
Begin
Select @count=@count+1 where datename(dw,dateadd(d,@i,@date1))
not in('Saturday','Sunday')
set @i=@i+1
End

RETURN @count

END

Select dbo.days_diff('04/09/2010','04/12/2010') as diff

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-15 : 02:13:27

quote:
Originally posted by clinton_eg

hi khtan

when i run:
select count(*)
from F_TABLE_DATE('20100409', '20100412')
where WEEKDAY_NAME not in ('Sat', 'Sun')

i get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'F_TABLE_DATE'.

Ewan Gilby

F_TABLE_DATE is a User_defined function! Have You create it??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-04-15 : 02:38:21

In short you will have to click here to get the function script of F_TABLE_DATE

PBUH

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 02:51:48

Hi idera
i just tried to create the function however get the following errors.

Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 472
Must declare the scalar variable "@start_date".
Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 473
Must declare the scalar variable "@end_date".
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'F_TABLE_DATE', because it does not exist or you do not have permission.
Checksum with ydm
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Checksum with ymd
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.

quote:
Originally posted by Idera

In short you will have to click here to get the function script of F_TABLE_DATE

PBUH

Ewan Gilby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 02:57:38

quote:
Originally posted by clinton_eg

Hi idera
i just tried to create the function however get the following errors.

Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 472
Must declare the scalar variable "@start_date".
Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 473
Must declare the scalar variable "@end_date".
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'F_TABLE_DATE', because it does not exist or you do not have permission.
Checksum with ydm
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Checksum with ymd
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.

quote:
Originally posted by Idera

In short you will have to click here to get the function script of F_TABLE_DATE

PBUH

Ewan Gilby

Your database is case-sensitive. for all the unknown variable or invalid column name error do a find and replace to change all to same case

KH
[spoiler]Time is always against us[/spoiler]

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 03:14:06

Thanks this worked

If i want to update a column (calculated column) in a table where the startdate and enddate is dynamic. Example each record has a different start date and enddate, could you let me know what approach should i take.

quote:
Originally posted by khtan

quote:
Originally posted by clinton_eg

Hi idera
i just tried to create the function however get the following errors.

Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 472
Must declare the scalar variable "@start_date".
Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 473
Must declare the scalar variable "@end_date".
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'F_TABLE_DATE', because it does not exist or you do not have permission.
Checksum with ydm
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Checksum with ymd
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.

quote:
Originally posted by Idera

In short you will have to click here to get the function script of F_TABLE_DATE

PBUH

Ewan Gilby

Your database is case-sensitive. for all the unknown variable or invalid column name error do a find and replace to change all to same case

KH
[spoiler]Time is always against us[/spoiler]

Ewan Gilby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 03:52:03

What is the version of the SQL Server you are using ?

to check the version,
print @@version

KH
[spoiler]Time is always against us[/spoiler]

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 04:27:01

Hi following is the details:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Ewan Gilby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 04:40:59

[code]
update t
set networkdays = d.days
from yourtable t
cross apply
(
select days = count(*)
from F_TABLE_DATE(start_date, end_date)
where WEEKDAY_NAME not in ('Sat', 'Sun')
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 05:17:24

Hi khtan
thanks for your help that works

. However if i want the formula only to run when the date is entered, how do i do that.
as runing the query with no task start date gives me an error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '

*******************************************************************
* Error in function F_TABLE_DATE:
* @FIRST_DATE cannot be null
*******************************************************************

' to data type int.

This is what im doing:
1.i upload the order number data which has the current date updated automatically.
2.agents scrub and check when a particular task is rasid, here the start date is entered.
3.i need to calculated the age of the task.
4.in some cases a task might not be in progress (In case of a process query), here there is no start date. however i need to calculate the Task age for the remaining records.

Could this be done? if yes, that will really help me

.
Appreciate your help with this

Ewan Gilby

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 05:38:49

Hi khtan

I have use the below query seems to work, let me know if this could cause any issues and/or if there is a better way to do this.

While (select count(*) from TABLE where startdate is NULL) = 0
BEGIN
update t set Age = d.days from TABLE t
cross apply
(select days = count(*) from F_TABLE_DATE(startdate,enddate)
where WEEKDAY_NAME not in ('Sat', 'Sun')) d
IF (select count(*) from TABLE where startdate is NULL) > 0
BREAK
END

Ewan Gilby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 05:39:40

add a condition to check that both start and end date must not be null before you use F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 05:54:11

the end date is the current which i update using the Default constraint.
unfortunately the query i gave does not update the data

Ewan Gilby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 06:05:27

[code]
update t
set networkdays = d.days
from yourtable t
cross apply
(
select days = count(*)
from F_TABLE_DATE(t.start_date, t.end_date)
where WEEKDAY_NAME not in ('Sat', 'Sun')
) d
where t.start_date is not null
and t.end_date is not null
[/code]

KH
[spoiler]Time is always against us[/spoiler]

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-15 : 08:15:00

Hi khtan

That helps, thanks a ton

Ewan Gilby

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-04-16 : 11:25:15

Hi Senthil, thanks this function also works great

quote:
Originally posted by senthil_nagore

Much Simple function as your requirement

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN

declare @i int
Declare @count int
declare @diff int
set @diff=datediff(d,@date1,@date2)
set @i=0
set @count=0
While(@i<@diff)
Begin
Select @count=@count+1 where datename(dw,dateadd(d,@i,@date1))
not in('Saturday','Sunday')
set @i=@i+1
End

RETURN @count

END

Select dbo.days_diff('04/09/2010','04/12/2010') as diff

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Ewan Gilby

Justice4243
Starting Member

1 Post

Posted - 2012-05-22 : 16:14:03

senthil_nagore's code worked great for me (though we didn't need Holidays).

One tweek is that datediff and NetworkDays look at dates slightly differently.

Network days counts the first date if it's a weekday, datediff never counts the first date.

I just added something to account for this (my changes in bold):

Create FUNCTION [usrrpt].[NetworkDays] ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN

declare @i int
Declare @count int
declare @diff int
set @diff=datediff(d,@date1,@date2)
set @i=0
set @count=0
While(@i<@diff)
Begin
Select @count=@count+1 where datename(dw,dateadd(d,@i,@date1))
not in('Saturday','Sunday')
set @i=@i+1
End

SET @count = CASE datepart(dw,getdate()) WHEN 1 THEN @count + 0
WHEN 7 THEN @count + 0
ELSE @count + 1 END

RETURN @count

END

But, this code was of great help!

LornaDooley
Starting Member

3 Posts

Posted - 2013-08-23 : 11:38:49

Found your post today, worked a treat! I have a Bank holiday table (ID column, and Date column), I now need advise of how to to exclude bank holidays in the datediff returned. Can anyone help please?

quote:
Originally posted by senthil_nagore

Much Simple function as your requirement

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN

declare @i int
Declare @count int
declare @diff int
set @diff=datediff(d,@date1,@date2)
set @i=0
set @count=0
While(@i<@diff)
Begin
Select @count=@count+1 where datename(dw,dateadd(d,@i,@date1))
not in('Saturday','Sunday')
set @i=@i+1
End

RETURN @count

END

Select dbo.days_diff('04/09/2010','04/12/2010') as diff

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources