<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
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 11 of 12
««
«
8
9
10
11
12
»»
SQL Function : Find ‘X’ Business Days in the Future
Rate Topic
Display Mode
Topic Options
Author
Message
michaelberry67
michaelberry67
Posted Wednesday, April 29, 2009 7:45 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 03, 2013 6:46 AM
Points: 6,
Visits: 55
I think you all my benefit from my function that was published by SQL server magazine:
http://www.sqlmag.com/Articles/ArticleID/97590/97590.html?Ad=1
Post #706813
Bob Griffin
Bob Griffin
Posted Wednesday, April 29, 2009 7:58 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:26 PM
Points: 1,132,
Visits: 663
You are the man....Rock on!
Post #706831
Jeff Moden
Jeff Moden
Posted Wednesday, April 29, 2009 8:40 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
michaelberry67 (4/29/2009)
I think you all my benefit from my function that was published by SQL server magazine:
http://www.sqlmag.com/Articles/ArticleID/97590/97590.html?Ad=1
Do you have any performance stats... say, on 100,000 rows?
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #706893
The Dixie Flatline
The Dixie Flatline
Posted Wednesday, April 29, 2009 9:11 AM
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 2:02 PM
Points: 3,788,
Visits: 5,543
I wasn't going to say it, Jeff.
If all the function is used for is to find a
single
date (like the first Tuesday of next month) which is then used in a where clause then a few milliseconds wouldn't matter. But you're right that there may be a problem when you need to calculate turntimes or lead times in business days for a large numbers of jobs.
__________________________________________________
Against stupidity the gods themselves contend in vain.
-- Friedrich Schiller
Stop, children, what's that sound?
-- Stephen Stills
Post #706928
michaelberry67
michaelberry67
Posted Wednesday, April 29, 2009 11:04 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 03, 2013 6:46 AM
Points: 6,
Visits: 55
The performance has been quite acceptable. If you need that specific logic it is fairly easy to read and you can pull it out if you wish. The main benefit is that it runs on a calendar table which you can query directly. Also, you can use it to get things like the 3rd working day skipping holidays and weekends. Try that with any other function.:)
Post #707066
The Dixie Flatline
The Dixie Flatline
Posted Wednesday, April 29, 2009 11:45 AM
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 2:02 PM
Points: 3,788,
Visits: 5,543
Michael, nobody is saying your baby is ugly.
Sure you are proud to have it published, but many of us already have calendar tables that make such queries trivial. In my current job, I've been asked twice to be able to determine the nTh weekday for a particular month. The first query took 5 minutes to write and debug and the second took less time to alter.
On the other hand my company has many report requirements for turntimes or lead times in business days, which may or may not include Saturdays, or holidays. A function that lets users specify the number of days and other options is appropriate there.
But, the same function that works acceptably for doing something one time may crawl if repeated a hundred thousand times. The issue Jeff raised is not whether or not your function works or is flexible. Restated, his question was "Is it suitable for mass volumes?" If not, it might work better if written as an inline table valued function for instance.
__________________________________________________
Against stupidity the gods themselves contend in vain.
-- Friedrich Schiller
Stop, children, what's that sound?
-- Stephen Stills
Post #707092
Jeff Moden
Jeff Moden
Posted Wednesday, April 29, 2009 11:54 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
Bob Hovious (4/29/2009)
But, the same function that works acceptably for doing something one time may crawl if repeated a hundred thousand times. The issue Jeff raised is not whether or not your function works or is flexible. Restated, his question was "Is it suitable for mass volumes?" If not, it might work better if written as an inline table valued function for instance.
Correct... I just suck at asking questions via post.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #707098
michaelberry67
michaelberry67
Posted Wednesday, April 29, 2009 2:48 PM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 03, 2013 6:46 AM
Points: 6,
Visits: 55
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
Post #707230
nroudak
nroudak
Posted Friday, January 08, 2010 9:21 AM
Grasshopper
Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 19,
Visits: 120
Hi Alex,
Could you please post here that function of yours that calcs biz day going into the past :)
Post #844418
mmiller-511987
mmiller-511987
Posted Monday, July 26, 2010 11:29 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, July 28, 2010 11:58 AM
Points: 4,
Visits: 51
Check this out... very nice: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html - skips weekends and could be easily retrofitted for calendar lookups.
Post #959187
« Prev Topic
|
Next Topic »
116 posts, Page 11 of 12
««
«
8
9
10
11
12
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
