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
Osman Sinan Guven
Osman Sinan Guven
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 89
The article "Temporal Data Techniques in SQL" (http://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/) might be a good read about alternative implementations.



Amit Lohia
Amit Lohia
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 174
This can be done without a loop.


Kindest Regards,

Amit Lohia
jh72i
jh72i
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 33
Only worry I would have would be about performance.
Koenraad Dendievel
Koenraad Dendievel
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 136
Don't forget that sunday isn't first day of week everywhere.
Some people start with monday (sunday is the 7th day)BigGrin
alex pilsworth
alex pilsworth
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 97
Hi J Gravelle,

I had a similar solution involving 2 functions - 1 to work out when the next working day is (including the current date) and 1 to apply this function x times over. The other difference is that this includes a country code to take into account differing international holidays.

I have also created the opposite functions to calculate x number of business days in the past (used to find all transactions x business days ago)

CREATE FUNCTION fnGetBusinessDateForward (@countryid int, @date date)
RETURNS date
AS

BEGIN
if datepart(dw, @date) in (1,7) or exists(select holidayid from tb_holiday where countryid=@countryid and holidaydate=@date) select @date = dbo.fnGetBusinessDateForward(@countryid, @date + 1)
return @date

END

CREATE FUNCTION fnGetTPlusBusinessDay (@date date, @countryid int, @tplus int)
RETURNS date
AS
BEGIN
while @tplus > 0
begin
SELECT @date = dbo.fnGetBusinessDateForward(@countryid, @date + 1), @tplus = @tplus - 1
end
return dbo.fnGetBusinessDateForward(@countryid, @date)
END
SQL_EXPAT
SQL_EXPAT
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 383
in the Exchange business we have the concept of a Trading Calendar. All trading days are listed in this table going 10 years into the future with a coresponding incrementing integer denoting the day.

DayNum TradingDate Created Modified DeletedFlag
.
.
7134 2009-01-28 00:00:00.000 2006-07-15 05:11:17.200 2009-02-04 01:01:37.643 0
7135 2009-01-29 00:00:00.000 2006-07-15 05:11:17.200 2009-02-04 01:01:37.643 0
7136 2009-01-30 00:00:00.000 2006-07-15 05:11:17.213 2009-02-04 01:01:37.643 0
7137 2009-02-02 00:00:00.000 2006-07-15 05:11:17.213 2009-02-04 01:01:37.643 0
7138 2009-02-03 00:00:00.000 2006-07-15 05:11:17.213 2009-02-04 01:01:37.643 0
7139 2009-02-04 00:00:00.000 2006-07-15 05:11:17.213 2009-02-04 01:01:37.643 0
.
.
.

Alan Cranfield

thanks

SQL_EXPAT
nigel.
nigel.
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2470 Visits: 2900
Amit Lohia (2/4/2009)
This can be done without a loop.


Any possibility you could expand on this?

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Michael Meierruth
Michael Meierruth
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1620 Visits: 2515
Amit Lohia (2/4/2009)
This can be done without a loop.

Are you sure?
Show us an example, or at least describe your approach conceptually.

The topic of adding business days to a date was hashed through a while ago. And this can be done without a loop. See Jeff Modem's example below. But when you also need to deal with holidays defined in a table it gets a bit complicated. And I don't see this possible without a loop.


CREATE FUNCTION addworkdays_jeff(@StartDate DATETIME, @DaysToAdd INT, @CountToday INT)
RETURNS DATETIME
AS
BEGIN --------------------------------------------------------------------------------------
RETURN (
SELECT d.EndDate
+ CASE WHEN DATENAME(dw,d.EndDate) = 'Saturday' THEN 2
WHEN DATENAME(dw,d.EndDate) = 'Sunday' THEN 1
ELSE 0
END AS EndDate
FROM (SELECT DATEADD(wk,@DaysToAdd/5, @StartDate
+ CASE WHEN DATENAME(dw,@StartDate) = 'Saturday' THEN 2
WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1
ELSE 0
END
+ @DaysToAdd%5-@CountToday
) AS EndDate
)d
) --End of Return
END --End of Function
go


Stuart Pearson
Stuart Pearson
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 137
In dealing with the holidays, rather than searching for each day in the loop why not just find the next holiday after the start and then check the loop date for a match against that.
Taking the original function-

CREATE function fn_AddBizDays
--Accepts Date ‘X’ and Integer ‘Y’

(
@dtBeginDate datetime,
@iBizDays int
)

RETURNS datetime
--Result is Date ‘Z’ that is ‘Y’ business days after supplied Date ‘X’

AS

BEGIN


DECLARE @dtEndDate datetime
DECLARE @dtDateHolder datetime
DECLARE @dtNextHol datetime

SET @dtDateHolder = @dtBeginDate
SET @dtEndDate = DATEADD(d,@iBizDays,@dtBeginDate)
--Find the first Public Holiday Date
select @dtNextHol = MIN(holiday_date) FROM tblHolidays where holiday_date >=@dtDateHolder

WHILE (@dtDateHolder <= @dtEndDate)
BEGIN

--Is the date being checked a Saturday or Sunday?
IF((DATEPART(dw, @dtDateHolder) in (1, 7))
--Is the date being checked a holiday?
OR (DATEADD(dd, 0, DATEDIFF(dd, 0, @dtDateHolder))=@dtNextPH))
--NOTE: DATEDIFF trick used above to discard TIMESTAMP

BEGIN

-- Extend the date range for Weekends and Holidays by one day
SET @dtEndDate = DATEADD(d,1,@dtEndDate)
if (DATEADD(dd, 0, DATEDIFF(dd, 0, @dtDateHolder))=@dtNextHol)
BEGIN
-- get the next public holiday date
select @dtNextHol = MIN(holiday_date ) FROM tblHolidays where holiday_date >@dtDateHolder
END
END

--Move to the next day in the range and loop back to check it
SET @dtDateHolder = DATEADD(d,1,@dtDateHolder)

END

--Respond with newly determined end date
RETURN @dtEndDate

END


Amit Lohia
Amit Lohia
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 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
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