find the next available business day

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

SQL Server 2000 Forums

Transact-SQL (2000)

find the next available business day

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-05-04 : 15:36:22

Hi, I created a Calendar table (found here [url]http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html[/url]) and loaded it with all the federal holidays from now until 2020.
I need to test a date and find the next available business day if necessary.
For example, if the due date falls on May 23, 24 or 25th, (Sat, Sun and a Holiday respectively) the function would give May 26th as the first available business day in each case.
I created a UDF to find the next available business day. It appears to work properly.
I was just wondering if there is a better way to do it...
Any suggestions are welcome (i.e. error checking, assignment of variables...)


create function dbo.IsBusinessDay
(
  @testdt SMALLDATETIME
)
RETURNS SMALLDATETIME 
AS 
BEGIN 
    DECLARE @hol bit
    DECLARE @wd  bit
    DECLARE @dy	 varchar(9)
    DECLARE @temp  SMALLDATETIME
	
    select @temp = (select dt from Calendar where dt = @testdt)
    select @hol = (select isHoliday from Calendar where dt = @testdt)
    select @wd = (select isWeekday from Calendar where dt = @testdt)	
    select @dy = (select dayname from Calendar where dt = @testdt)

    while (@hol = 1 or @wd = 0)
	Begin
	    if @dy = 'Saturday'
	    begin
		set @temp = DateAdd("d", 2, @temp)
	    end	
	    if (@dy = 'Sunday') or (@hol = 1)
	    begin
		set @temp = DateAdd("d", 1, @temp)
	    end	  
	select @temp = (select dt from Calendar where dt = @temp)
    	select @hol = (select isHoliday from Calendar where dt = @temp)
    	select @wd = (select isWeekday from Calendar where dt = @temp)	
    	select @dy = (select dayname from Calendar where dt = @temp)    
	end

    RETURN(@temp) 
END

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-04 : 15:46:25

[code]create function dbo.IsBusinessDay
(
@testdt SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE @temp SMALLDATETIME
select @temp = min(dt) from Calendar where dt>= @testdt and isHoliday=0 and dayname not in('Saturday','Sunday')
RETURN(@temp)
END[/code]Warning: not tested, but should accomplish the same thing.

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-05-04 : 16:02:10

Hey thanks!
That was way better than what I did.
So I didn't need the UDF, I could have used a stored proc instead, right?

Thanks again

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-04 : 16:02:40

Here is another way to do it.


drop function dbo.IsBusinessDay
(
  @testdt SMALLDATETIME
)
RETURNS SMALLDATETIME 
AS 
BEGIN

DECLARE @temp  SMALLDATETIME

select top 1
	@temp = dt
from
	Calendar
where
	dt>= @testdt and
	isHoliday=0 and
	dayname not in('Saturday','Sunday') 
order by
	dt

RETURN(@temp)

END

CODO ERGO SUM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-04 : 16:43:23

quote:
So I didn't need the UDF, I could have used a stored proc instead, right?

It's up to you, whichever is easiest for you and your coworkers to understand.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources