SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Function : Find ‘X’ Business Days in the Future


SQL Function : Find ‘X’ Business Days in the Future

Author
Message
Amit Lohia
Amit Lohia
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2996 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
Michael Meierruth
Michael Meierruth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4062 Visits: 2522
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.
Amit Lohia
Amit Lohia
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2996 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
Michael Meierruth
Michael Meierruth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4062 Visits: 2522
Prove it!
klopez-802826
klopez-802826
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 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.
mahesh.mangaonkar
mahesh.mangaonkar
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Michael Meierruth
Michael Meierruth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4062 Visits: 2522
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.
klopez-802826
klopez-802826
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 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.
MichaelBerry
MichaelBerry
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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
pegels
pegels
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search