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 «««89101112»»

SQL Function : Find ‘X’ Business Days in the Future Expand / Collapse
Author
Message
Posted Wednesday, April 29, 2009 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:57 PM
Points: 7, Visits: 79
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
Posted Wednesday, April 29, 2009 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 10:01 AM
Points: 1,136, Visits: 697
You are the man....Rock on!
Post #706831
Posted Wednesday, April 29, 2009 8:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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 "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #706893
Posted Wednesday, April 29, 2009 9:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:24 PM
Points: 4,011, Visits: 6,082
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
Posted Wednesday, April 29, 2009 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:57 PM
Points: 7, Visits: 79
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
Posted Wednesday, April 29, 2009 11:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:24 PM
Points: 4,011, Visits: 6,082
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
Posted Wednesday, April 29, 2009 11:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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 "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #707098
Posted Wednesday, April 29, 2009 2:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:57 PM
Points: 7, Visits: 79
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
Posted Friday, January 8, 2010 9:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 10:51 AM
Points: 25, Visits: 126
Hi Alex,
Could you please post here that function of yours that calcs biz day going into the past :)
Post #844418
Posted Monday, July 26, 2010 11:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase «««89101112»»

Permissions Expand / Collapse