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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: Administrators
Points: 17773 Visits: 196
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-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

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

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


Kindest Regards,

Amit Lohia
jh72i
jh72i
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 33
Only worry I would have would be about performance.
Koenraad Dendievel
Koenraad Dendievel
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 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 Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1502 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.
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5988 Visits: 2972
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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4934 Visits: 2526
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 Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

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