SQL Function : Find ‘X’ Business Days in the Future

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by J Gravelle » SQL Function : Find ‘X’ Business Days in the...

116 posts, Page 12 of 12

«««8 9 10 11 12

SQL Function : Find ‘X’ Business Days in the Future

Rate Topic

Display Mode

Topic Options

Author

Message

mmiller-511987

Posted Monday, July 26, 2010 11:31 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 11:58 AM
Points: 4, Visits: 51

Great way to do date advancement: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html

Post #959189

The Dixie Flatline

Posted Tuesday, July 27, 2010 7:29 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:08 PM
Points: 3,788, Visits: 5,537

michaelberry67 (4/29/2009)

Well it is slow as most in-line functions are, but that is the nature of the beast I guess. It has worked well with larger output sets, but I have not benchmarked it. If you wanna...:) be my guest.

I do love this website though. Great information continually!
Mike

By the way, inline table valued functions are NOT slow. Multi-statement table valued functions are, but I've used inline functions many many times to replace user-defined functions and speed up queries.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills

Post #959375

Michael Meierruth

Posted Tuesday, July 27, 2010 7:37 AM

SSC-Addicted

Group: General Forum Members
Last Login: Sunday, May 05, 2013 10:12 AM
Points: 480, Visits: 1,604

mmiller-511987 (7/26/2010)

Great way to do date advancement: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html

I don't think using recursive CTE is a good approach for doing this kind of date arithmentic.
Take a look at Jeff Modem's example earlier in this post.
I ran his against yours and his is 65 times faster!

Post #959387

The Dixie Flatline

Posted Tuesday, July 27, 2010 7:41 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:08 PM
Points: 3,788, Visits: 5,537

Recursive CTEs are rarely the fastest performing way of getting something done.

Again, a calendar table solution will be both fast and flexible.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills

Post #959390

mmiller-511987

Posted Wednesday, July 28, 2010 12:12 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 11:58 AM
Points: 4, Visits: 51

Wasn't even thinking of a table driven way of date evaluation. I like it though, must of took some time to write that.

FYI: By adjusting the CTE to a week by week recursion rather than each day I was able to get performance 3x faster. A 1000 day recursion resulted on average a 0.009375 second penalty per invocation (on a moderately speced machine.) I will update that blog when I have time to get the negative advancement complete. Still I'm sure the table driven method is still 20x faster; not to mention all the extra benefits incurred from his toolset.

Thanks for replying!

Post #960274

The Dixie Flatline

Posted Wednesday, July 28, 2010 12:34 PM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:08 PM
Points: 3,788, Visits: 5,537

One of the wonderful things about table-driven solutions is how easy the coding becomes once the table is in place. However, I'm bookmarking this to go back and take a look at your recursive solution.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills

Post #960297

« Prev Topic | Next Topic »

116 posts, Page 12 of 12

«««8 9 10 11 12

Permissions