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.