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
Michael Meierruth
Michael Meierruth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1344 Visits: 2515
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1180 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1344 Visits: 2515
Prove it!
klopez-802826
klopez-802826
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: 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
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1344 Visits: 2515
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 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: 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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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?
Zanoni Labuschagne-766625
Zanoni Labuschagne-766625
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 75
Hi, J Gravelle

There is a data warehousing structure that will make it much easier to do this calc, as well as the flexibility to do loads more...The Date dimension

By using a date dimension, you won't need to loop, and can use one set-based statement to get the total.

Just some info on a date dimension - essentially, a date dimension is a table that allows you to set certain metadata around a date. You can for instance indicate whether a date is a week day, holiday, etc.

An example of a date dimension is:

Create table DimDate
(
DateID int Primary Key
-- Generally the format yyyymmdd, e.g. 20081201 for 1 Dec 2008
,DateStamp DateTime
,Year_ID int
,Month_Name varchar(200)
,Month_ID int
,Day_ID int
,IsWeekDay bit
,IsHoliday bit
)
go

-- Populate the table using a MDM / SQL tool / script

Then populate the table with all the dates between a specific range e.g. 1 Jan 2000 to 31 Dec 2020


You can now at a glance see if a date is a holiday or a work day.

Now, if you want to calc the date of "x" working days forward, you can write a simple function like the one below (there are probably LOADS of better ways than what I did below, but this was the first way that came to mind).

Cheers.

Zanoni Labuschagne


-- Function Code

creaet function fn_CalcDate
(
@StartDate datetime
,@intDays int
)
Returns DateTime
as
begin
Declare @ReturnDate datetime
,@intRows int

declare @DateRange table
(
RowID int identity(1,1)
,DateStamp datetime
)

Insert into @DateRange
(
DateStamp
)
Select DateStamp
from DimDate
where isWeekday = 1
and isHoliday = 0
Order by DateStamp

Select @intRows = max(RowID)
from @DateRange

if @intDays <= @intRows
-- if the # of days in the table var is more than the days used to calc, find the relevant row
begin
Select @ReturnDate = DateStamp
from @DateRange
Where @intRows = RowID
end
else
-- if the days in the table is less, use the last
begin
Select @ReturnDate = max(DateStamp)
from @DateRange
end

Return @ReturnDate

end


_____________________________________________________________________
Select 'Remember, we were all newbies once'
From Truth
Order by Experience

_____________________________________________________________________

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