Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL Function : Find ‘X’ Business Days in the Future Expand / Collapse
Author
Message
Posted Tuesday, February 3, 2009 9:34 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item SQL Function : Find ‘X’ Business Days in the Future
Post #649517
Posted Wednesday, February 4, 2009 1:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, May 18, 2014 1:55 AM
Points: 68, 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.


Post #649568
Posted Wednesday, February 4, 2009 1:26 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174
This can be done without a loop.


Kindest Regards,

Amit Lohia
Post #649576
Posted Wednesday, February 4, 2009 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 26, 2010 12:01 PM
Points: 5, Visits: 33
Only worry I would have would be about performance.
Post #649579
Posted Wednesday, February 4, 2009 2:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:32 AM
Points: 14, Visits: 130
Don't forget that sunday isn't first day of week everywhere.
Some people start with monday (sunday is the 7th day):D
Post #649592
Posted Wednesday, February 4, 2009 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:53 AM
Points: 5, Visits: 83
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
Post #649615
Posted Wednesday, February 4, 2009 3:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:09 PM
Points: 136, 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
Post #649626
Posted Wednesday, February 4, 2009 4:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:39 AM
Points: 1,181, Visits: 2,651
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

Post #649656
Posted Wednesday, February 4, 2009 4:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 536, Visits: 2,103
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

Post #649662
Posted Wednesday, February 4, 2009 4:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:23 AM
Points: 13, Visits: 120
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

Post #649670
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse