Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Finding the Next Business Day Recursively Expand / Collapse
Author
Message
Posted Friday, November 4, 2005 7:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 4, 2013 4:01 AM
Points: 20, Visits: 7

Here's another function that takes a simpler approach. If you are maintaining a holiday table, it would be simple enough to check the return value against it as well.

 

John

 

CREATE       FUNCTION dbo.udf_GetNextBizDay (@dDateValue DATETIME)
RETURNS Datetime
AS 
---------------------------------------------------------------------------------
--
-- Modification History:
--
-- Date                     Version  Programmer Comments
-- ------------------       -------  ---------- ----------------------------
-- 2005-06-13 02:23:00  1.0  John McLaughlin 
--
--
BEGIN

 DECLARE @dReturnDate DATETIME
 

 IF ISDATE(@dDateValue)=0
  BEGIN
   RETURN NULL
  END
 
 SET @dReturnDate = DATEADD(d,1,@dDateValue)
 --Set to zero hour. Uncomment if needed.
 --SET @dReturnDate = CAST(CONVERT(CHAR(10),@dReturnDate,101) AS DATETIME)
 WHILE DATEPART(dw,@dReturnDate) in(1,7)
  BEGIN
   SET @dReturnDate = DATEADD(d,1,@dReturnDate)
  END
 


 RETURN @dReturnDate
END

Post #235113
Posted Friday, November 3, 2006 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:12 PM
Points: 1,945, Visits: 2,937
It is a cute trick, but you would never use it in the real world. A calendar table is easier, cleaner, portable and faster than this procedural code. We avoid functions in SQL in favor of a data-driven solution -- it is not a computational language.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #320289
Posted Friday, November 3, 2006 9:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 10:14 AM
Points: 18, Visits: 167

I like the trick for handling the DATEFIRST.  With it, you can write the function in 3 lines without recursion, which I think is a bit more elegant.

CREATE FUNCTION dbo.fnGetNextBusinessDay (@Date datetime) RETURNS datetime AS
BEGIN
     SET @Date = DATEADD(dd, 1, CONVERT(datetime, (CONVERT(CHAR(10), @Date, 101))))
     WHILE ((@@DATEFIRST + DATEPART(dw, @Date) - 1) % 7) + 1 IN (1, 7) OR @Date IN (SELECT HolidayDate FROM Holiday) SET @Date = DATEADD(dd, 1, @Date)
     RETURN @Date
END

 

Post #320361
Posted Friday, November 3, 2006 9:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612

Hey Joe, don't you feel like you are talking for nothing sometimes???

 

Guys, Joe literally wrote the book on SQL, he wrote the ISO standards for SQL.  I would strongly suggest you listen to him on this one... and basically any other post he makes .

 

Even if it seems like overkill this time, and the next time and the time after that.  Won't you wish you had taken the time to write one when you decid to implement it the nth time... and then decid to make that the business rule to implement overall once you see the benefits??

Post #320377
Posted Thursday, November 9, 2006 5:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:04 PM
Points: 871, Visits: 282

I don't understand the @numDays input parameter!

For example if do this,

DECLARE @Date SMALLDATETIME

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 10)

SELECT @Date

It's suppsoe to return the date 2006-12-27 as that is the next business day.

Can someone explain please?




Kindest Regards,



Post #321807
Posted Friday, November 10, 2006 1:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,977, Visits: 8,239

Hi Trigger,

The @numDays parameter is the number of business days to move forward in time.

To get 2006-12-27, you should have used:

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 1)

to get the "first" next business day. Your use of:

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 10)

should get the 10th next business day.




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #321870
Posted Friday, January 5, 2007 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 7:09 AM
Points: 4, Visits: 17
One minor problem in the recursive part of the function
The where clause 'holidayDate=@nextBusDay' will not evaluate
correctly if the @NextbusDay has a time part.
Need to change this to normalize the time part for both dates to 00:00:00 before comparing.


I loved the article though.Good work Rob.
Post #334746
Posted Thursday, April 26, 2007 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 4:49 PM
Points: 5, Visits: 33

This did not do it for me:

select dbo.GetNextBusinessDay(getdate(),1)
select dbo.GetNextBusinessDay(getdate()+1,1) -- Fri
select dbo.GetNextBusinessDay(getdate()+2,1) -- Sat
select dbo.GetNextBusinessDay(getdate()+3,1) -- Sun

                                                     
------------------------------------------------------
2007-04-27 12:41:00

(1 row(s) affected)

                                                      
------------------------------------------------------
2007-04-30 12:41:00

(1 row(s) affected)

                                                      
------------------------------------------------------
2007-04-29 12:41:00

(1 row(s) affected)

                                                      
------------------------------------------------------
2007-04-30 12:41:00

(1 row(s) affected)

Post #361241
Posted Wednesday, September 19, 2007 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 7, 2010 1:55 PM
Points: 1, Visits: 29
I have the same problem as Thomas Nielson, it does not return the right value when the first parameter is a saturday try 3/31/2007, it returns 4/1 when it should return 4/2.
Post #400424
Posted Thursday, September 20, 2007 10:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:12 PM
Points: 1,945, Visits: 2,937
There is no need for recursion. I am going to assume that you have a Sequence auxiliary table with enough rows for your needs -- say integers 1 to 50,000.

-- create Weekdays table
CREATE TABLE Weekdays
(cal_date DATETIME NOT NULL PRIMARY KEY,
julian_date INTEGER NOT NULL);

--julianize weekdays for, say, ten years
INSERT INTO Weekdays
SELECT DATEADD(DD, seq, '2000-01-01'), seq
FROM Sequence
WHERE DATEADD(DD, seq, '2000-01-01') <= '2010-01-01'
AND DATENAME(DW, DATEADD(DD, seq, '2000-01-01'))
NOT IN ('Sunday', 'Saturday');

--julianize weekends

INSERT INTO Weekdays -- add Saturdays
SELECT DATEADD(DD, 1, cal_date), julian_date
FROM Weekdays
WHERE DATENAME(DW, cal_date) = 'Friday';

INSERT INTO Weekdays -- add Sundays
SELECT DATEADD(DD, 1, cal_date), julian_date
FROM Weekdays
WHERE DATENAME(DW, cal_date) = 'Saturday';

Get a spreadsheet and build a list of company holidays for the years we are worried about. Create a script with calls to a procedure that re-numbers the Julianized days:

CREATE PROCEDURE AddHoliday (@new_holiday DATETIME)
AS
UPDATE Weekdays -- should now be business days
SET julian_date = julian_date - 1
WHERE cal_date >= @new_holiday;

you can also write a RemoveHolidsay (@old_holiday DATETIME) procedure in case we drop one (remember when we consolidated Lincoln and Washington's birthdays?)



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #401006
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse