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 Monday, July 26, 2010 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:22 AM
Points: 4, Visits: 52
Great way to do date advancement: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html
Post #959189
Posted Tuesday, July 27, 2010 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 2:31 PM
Points: 2,804, Visits: 6,155
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
Posted Tuesday, July 27, 2010 7:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 26, 2015 11:06 AM
Points: 564, Visits: 2,330
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
Posted Tuesday, July 27, 2010 7:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 2:31 PM
Points: 2,804, Visits: 6,155
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
Posted Wednesday, July 28, 2010 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:22 AM
Points: 4, Visits: 52
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
Posted Wednesday, July 28, 2010 12:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 2:31 PM
Points: 2,804, Visits: 6,155
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
Posted Friday, February 13, 2015 4:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 25, 2015 12:14 PM
Points: 38, Visits: 76
hi,

If possible can you share tb_holiday script.

I need to come up with table or function to achieve the following description. I believe the functions you mentioned here are similar to like this. can you help how to create in tsql. Many Thanks


We have a requirement to show 'Working date' as a output for the below input parameters

Input
a) Input Date (Date format)
b) Factory Calendar (2 digit char)
c) Days (Either minus or plus in days)

Output
Date (Date format)
Post #1660238
Posted Saturday, February 14, 2015 12:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 2:31 PM
Points: 2,804, Visits: 6,155
Wow.... five years...

Here is a quick example of a Calendar table. If you will search this site under the words "Calendar Table" you will find a number of articles with more in depth explanations.


create Table dbo.Calendar -- some people call this a date table
( CalendarDate date
,Weekend bit
,Holiday bit
,EndOfMonth bit -- handy to simplify calculations involving end of month
)
GO

-- normally you'd insert a couple of year's worth of data into the table so you can look month's into the future if necessary
-- (use a tally table to increment getdate() to populate it quickly, then go back and do updates to set the bits)
-- but for our example purposes, we'll just put a few day's worth and pretend Tuesday is a holiday

insert into calendar
values ('2015-02-12',0,0,0),('2015-02-13',0,0,0),('2015-02-14',1,0,0),('2015-02-15',1,0,0), ('2015-02-16',0,0,0),('2015-02-17',0,1,0),('2015-02-18',0,0,0)

select * from Calendar


-- for this example, the definition of a workday is a day that is neither a weekend day, nor a holiday
-- so to find the number of workdays between Thursday the 12th and Wednesday the 18th (inclusive) simply run the following query.
-- although seven days are included in the BETWEEN range, three non-work days are omitted as either holidays or weekend days

select COUNT(*) as workdays
from Calendar
where Calendardate between '2/12/2015' and '2/18/2015'
and Weekend <> 1 and Holiday <> 1






__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1660530
« Prev Topic | Next Topic »

Add to briefcase «««89101112

Permissions Expand / Collapse