Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL Function : Find ‘X’ Business Days in the Future Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, February 4, 2009 4:49 AM
 Say 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 Group: General Forum Members Last Login: Wednesday, November 16, 2016 11:51 PM Points: 571, Visits: 2,502
 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 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 Group: General Forum Members Last Login: Wednesday, November 16, 2016 11:51 PM Points: 571, Visits: 2,502
 Prove it!
Post #649685
 Posted Wednesday, February 4, 2009 5:41 AM
 Forum 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 daysThe 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 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 Group: General Forum Members Last Login: Wednesday, November 16, 2016 11:51 PM Points: 571, Visits: 2,502
 Above Amit Lohia says:For example, if there is a holiday between the dtEndDate from step 2 and dtEndDate from step 3This 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 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+14. 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 Group: General Forum Members Last Login: Tuesday, December 8, 2015 7:52 AM Points: 2, Visits: 161
 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 Group: General Forum Members Last Login: Thursday, June 2, 2016 8:47 AM Points: 4, Visits: 44
 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

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.