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

SQL Function : Find ‘X’ Business Days in the Future Expand / Collapse
Author
Message
Posted Wednesday, February 4, 2009 4:49 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174
Nigel Rutter (2/4/2009)
Amit Lohia (2/4/2009)
This can be done without a loop.


Any possibility you could expand on this?


Here is the logic but if you want me to write the script let me know.

Calculate dtEndDate by Simply adding dtBeginDate + iBizDays.
Find number of records between dtBeginDate and dtEndDate from your holiday table (inclusive of both dtBeginDate and dtEndDate).
Add that number to your dtEndDate.

Note: There is a flaw with this approach but I think we also be handled by modifying the table.
For example, if there is a holiday between the dtEndDate from step 2 and dtEndDate from step 3.


Also, You can always add weekends as part of your table.



Kindest Regards,

Amit Lohia
Post #649671
Posted Wednesday, February 4, 2009 5:04 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: Yesterday @ 7:36 AM
Points: 546, Visits: 2,147
It's the flaw you mentioned that makes a loop necessary.
And adding weekends to you holiday table is not going to solve that either.
Post #649680
Posted Wednesday, February 4, 2009 5:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174
I am sure it can be done by modifying the table. If we have a table, logic and calculation can be part of the table or avoid any calculation or loop.

Adding weekend to table is not to solve the flaw which I mentioned but to avoid checking saturday and Sunday logic.



Kindest Regards,

Amit Lohia
Post #649683
Posted Wednesday, February 4, 2009 5:16 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: Yesterday @ 7:36 AM
Points: 546, Visits: 2,147
Prove it!
Post #649685
Posted Wednesday, February 4, 2009 5:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 4, 2009 10:22 AM
Points: 2, Visits: 78
There should still be no need for loops. Amit Lohia's response was closest to what I figured a non-loop solution would be. If the "flaw" he refers to are holidays that fall on weekends, the solution is simple. Have your table contain the holiday's date and weekday, SELECT and COUNT() those entries that don't fall on weekends, then you may count the number of weekends within your time period and voilà, you have your count.

So deadlineDate = dtStart + iBizDays + modified table count + count of weekend days

The only thing you have to be careful about are off-by-one issues that may arise with dtEnd and/or dtStart falling on weekends and/or holidays, but those are logic issues that don't need loops for a resolution.

If there is yet another flaw in that logic, please let me know. I'm sure it can still be resolved functionally instead of procedurally.
Post #649705
Posted Wednesday, February 4, 2009 5:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 7, 2009 8:19 AM
Points: 3, Visits: 11
Hi,

Could you help me know what should be done, in case the Start Date is a weekend or holiday ? Shouldn't the 'add 1' logic be applicable there as well ?

Cheers,
Mahesh
Post #649716
Posted Wednesday, February 4, 2009 6:12 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: Yesterday @ 7:36 AM
Points: 546, Visits: 2,147
Above Amit Lohia says:

For example, if there is a holiday between the dtEndDate from step 2 and dtEndDate from step 3

This is the correct diagnosis of the flaw. But the solution is only of a recursive type and thus implies a loop.
Let's say you fix this one. You found 10 holidays between the two end dates and add 10.
But holidays may be again found between these two end dates and you have to start all over again in fixing things.
When you have finally reached the part of the loop where there are no more holidays between the two end dates you are finally done. But you needed a loop to get there.
Post #649727
Posted Wednesday, February 4, 2009 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 4, 2009 10:22 AM
Points: 2, Visits: 78
Actually, after careful study of the example the article listed:

For example, if a case was opened on Christmas Eve Day (12/24/2008) and my client was contractually obligated to resolve the issue within 5 business days, the SLA deadline would be January 2nd. But a six-day deadline wouldn’t come due until January the 5th.


This implies that the current date is not counted regardless of its holiday status. We begin counting from the next business day as day 1. (In this case 12/26 is the first business day, and if you had 1 business day to resolve the problem, it would be due on that day).

The real flaw in the current logic is that you don't really know what the end date should be in the first place, so there is no real time period on which to base your holiday and weekend count queries.

Solving this issue would require a little bit of SQL trickery (assuming MSSQL will handle the queries -- I haven't tested it):
1. Build a cross join view that contains all the days in a year for the next few years (based on the lifetime of the code? 100 years should suffice.)
2. Build a weekends and holidays table that merges all upcoming holidays with all weekends, removing duplicate dates (this can also be a view, and a very large set, the query optimizer will later ignore most of the set and focus on the values used in the query).
3. do a SELECT TOP iBizDays of the SET difference of table 1 - table 2 (I think you'd use the DIFFERENCE key word) WHERE date > dtStart ORDER BY DATE DESC (I think it's DESC, it might be ASC, basically get the first 'iBizDays' records starting from dtStart+1
4. from table #3 select the one with the latest date. It would be TOP 1 in the reverse order from table #3. These steps must be done in that order.

That should give you your desired date.
Post #649738
Posted Wednesday, February 4, 2009 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2014 1:00 PM
Points: 2, Visits: 140
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 #649770
Posted Wednesday, February 4, 2009 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 10, 2012 5:13 AM
Points: 4, Visits: 42
Well ... how does this RBAR approach perform for hundreds of thousands of lines?

What about using something like a Tally table with dates INNER JOINed to a table with holidays?

Post #649786
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse