Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Finding the Next Business Day Recursively Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 13, 2005 1:03 PM
 Grasshopper Group: General Forum Members Last Login: Tuesday, November 12, 2013 9:15 AM Points: 11, Visits: 245
Post #228794
 Posted Thursday, November 03, 2005 12:40 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, March 05, 2012 12:42 PM Points: 8, Visits: 17
 I have to quibble a bit on the definition of recursion and the article provides a hint to support this."Way back when", recursion was taught as "a function which calls itself with parameters|data|whatever in a simpler version of themselves [compared to what was passed in].  Eventually, you will reach a base or termination case which will cause the recursion to unravel itself and provide a solution.
Post #234745
 Posted Thursday, November 03, 2005 12:56 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, December 23, 2008 3:10 PM Points: 23, Visits: 17
 Rob,  for some reason your function didn't work for me. I changed it a bit to get it working here and changed basically two things:1) As we are using recursion there is no need for an explicit loop inside the function. The iteration is done using the consecutive calls to the function.2) I get rid of the HH:MM:SS of the startDate in order to compare with the dates on the Holiday table. Otherwise the comparison would be like '2005-11-07 16:41:03' = '2005-11-07 00:00:00', which would evaluate to FALSE.Below is the code with the changes.Cheers,Andrecreate function fnGetNextBusinessDay (@startDate smalldatetime) returns smalldatetime as Begin Declare @nextBusDay smalldatetime Declare @weekDay tinyInt set @nextBusDay = convert(datetime,left(convert(varchar,@startDate + 1,120),10),120) -- first get the raw next day SET @weekDay =((@@dateFirst+datePart(dw,@nextBusDay)-2) % 7) + 1 if @weekDay in (6, 7) or exists (select 1 from holiday where holidayDate = @nextBusDay) set @nextBusDay = dbo.fnGetNextBusinessDay(@nextBusDay) return (@nextBusDay) End
Post #234747
 Posted Thursday, November 03, 2005 2:13 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 10:33 AM Points: 5,597, Visits: 7,797
 Hi Rob,While the function uses some nifty tricks (I particularly like the trick to get correct results regardless of datefirst setting; this is a new trick for me), I'd never recommend using this function to anyone.There are other ways to achieve this. Ways that are, in my opinion, better. Check out http://www.aspfaq.com/show.asp?id=2519, and especially the section titled "Pre-determine delivery dates".Best, Hugo Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #234760
 Posted Thursday, November 03, 2005 3:07 AM
 Ten Centuries Group: General Forum Members Last Login: Sunday, September 16, 2012 3:26 AM Points: 1,038, Visits: 443
 Elegant solution (I too like the trick to get the correct day regardless of datefirst!  Clever!).  BUT...  recursion in SQL is limited to 32 calls (XMAS holidays in schools, for example, go a lot longer than this) - it could easily have been done with a simple loop, or better yet, a simple select statement...In our software where we have to keep track of days a clinic is open for business, rather than messing around with such procedures, we just have a dates table with one value for each day and a bit flag representing opened or closed...  Makes reporting VERY easy and you can join to the table easily to group days together efficiently, etc.  For similar ideas and more info on why just storing the data in a table can be efficient, look up "numbers table" on this site - Adam Machanic has quite a bit written about them.But I still like that modulus trick!!
Post #234776
 Posted Thursday, November 03, 2005 3:22 AM
 Grasshopper Group: General Forum Members Last Login: Monday, July 15, 2013 3:22 AM Points: 19, Visits: 30
 Cute, but I agree with Ian, if you are using a table why not maintain the correct dates in the table, it is MUCH easier to work with.I did not know the 32 levels of recursion limitation, thanks
Post #234783
 Posted Thursday, November 03, 2005 5:38 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, November 24, 2011 2:31 AM Points: 169, Visits: 156
 One thing to note. @@Datefirst is affected by things like the defaultlanguage property of your language.I believe that for the US @@Datefirst defaults to 7, whereas if you set up your login to use British English, as we brits often do, it will be 1.This will affect what Datepart(weekday, getdate()) returns.Just something to be aware of, I've had problems with this before.
Post #234814
 Posted Thursday, November 03, 2005 5:50 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, March 08, 2013 11:15 AM Points: 440, Visits: 1,785
 Paul Cresham posted an elegant solution for this including working out holidays on the fly, albeit for the UK.  It did contain a nifty Easter algorithm, as well as tackling the @@DATEFIRST problem.  see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=110259I documented it with an example of how we use it at http://glossopian.co.uk/pmwiki.php?n=Main.WorkingDaysDave Jackson http://glossopian.co.uk/"I don't know what I don't know."
Post #234816
 Posted Thursday, November 03, 2005 9:14 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 Here's another version of the Easter Calculation algorithm:http://sqlservercentral.com/cs/blogs/michael_coles/archive/2005/07/16/49.aspxAnd something for calculating "floating" American holidays:http://sqlservercentral.com/cs/blogs/michael_coles/archive/2005/07/17/52.aspx
Post #234890
 Posted Thursday, November 03, 2005 9:32 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, March 12, 2010 12:16 PM Points: 187, Visits: 15
 Rob- I like this example. It will work for our business because we don't take long holidays Thanks, Matt Matt Dolan
Post #234901

 Permissions