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
Site Owners
Site Owners
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: Administrators
Points: 13569 Visits: 55
Comments posted to this topic are about the item SQL Function : Find ‘X’ Business Days in the Future
Osman Sinan Guven
Osman Sinan Guven
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

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


Kindest Regards,

Amit Lohia
jh72i
jh72i
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 33
Only worry I would have would be about performance.
Koenraad Dendievel
Koenraad Dendievel
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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 (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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 Eights!
SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)

Group: General Forum Members
Points: 850 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.
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3911 Visits: 2922
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
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: 2968 Visits: 2519
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
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

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


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