Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 fn_next_business_day

Author  Topic 

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 07:22:54
Calculate the next x business day (excluding Sat, Sun). Basically a businessdateadd() for dateadd()


IF EXISTS (SELECT * FROM sysobjects WHERE xtype = 'FN' AND name = 'fn_next_business_day')
BEGIN
DROP FUNCTION fn_next_business_day
END
go

CREATE FUNCTION fn_next_business_day
(
@start_date datetime,
@days int -- no of business days to add
)
RETURNS datetime
AS
BEGIN
DECLARE @wd int

-- get the weekday AND CONVERT to datefirst = 1 value
SELECT @wd = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1

-- IF it IS Sat, Sun, change teh date to Next Monday
IF @wd IN (6, 7)
BEGIN
SELECT @start_date = DATEADD(DAY, 7 - @wd + 1, @start_date)
SELECT @wd = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1
END

RETURN
(
SELECT DATEADD(DAY,
@days + CASE WHEN @days >= (5 - @wd + 1)
THEN ((@days + ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1 - 1)/ 5) * 2
ELSE 0
END,
@start_date)
)
END
go

/* testing */
DECLARE
@start_date datetime

SELECT @start_date = '2009-05-11'

SELECT start_date = d.start_date,
start_wd = DATENAME(weekday, d.start_date),
days = d.days,
business_day = dbo.fn_next_business_day(d.start_date, d.days),
business_wd = DATENAME(weekday, dbo.fn_next_business_day(d.start_date, d.days))
FROM (
SELECT start_date = DATEADD(DAY, s.NUMBER, @start_date),
days = d.NUMBER
FROM (
SELECT NUMBER = 0 UNION ALL SELECT NUMBER = 1 UNION ALL SELECT NUMBER = 2 UNION ALL
SELECT NUMBER = 3 UNION ALL SELECT NUMBER = 4 UNION ALL SELECT NUMBER = 5 UNION ALL
SELECT NUMBER = 6
) s
CROSS JOIN dbo.F_TABLE_NUMBER_RANGE(0, 15) d
) d
ORDER BY d.start_date, d.days




KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 07:23:52
Peter or anybody with a more efficient way ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 09:14:56
Yes?

I believe your function calculates the wrong date. Using your sample data, see rows with Days value {5,6,7,8} for StartDate of '20090511'.
Rows with Days value {6,7} should all be Monday 18th, right?

It all depends on if Days parameter is for adding total days or adding business days.
CREATE FUNCTION	dbo.fnNextBusinessDay
(
@theDate DATETIME,
@theDays SMALLINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Offset TINYINT,
@ApproxDate DATETIME

SELECT @ApproxDate = DATEADD(DAY, @theDays, @theDate),
@Offset = DATEDIFF(DAY, -53690, @ApproxDate) % 7

RETURN DATEADD(DAY, (@Offset / 5) * (7 - @Offset), @ApproxDate)
END
Or as inline
CREATE FUNCTION	dbo.fnNextBusinessDay
(
@theDate DATETIME,
@theDays SMALLINT
)
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT DATEADD(DAY, (Offset / 5) * (7 - Offset), ApproxDate)
FROM (
SELECT DATEADD(DAY, @theDays, @theDate) AS ApproxDate,
DATEDIFF(DAY, -53690, DATEADD(DAY, @theDays, @theDate)) % 7 AS Offset
) AS d
)
END


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 09:26:54
thanks for the feedback. I will take a look at it


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-03 : 09:30:55
Edit: sorry, didn't check peso's reply and understood what this function is doing. sorry for that.

--------------------
Rock n Roll with SQL
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 09:32:12
quote:
I believe your function calculates the wrong date. Using your sample data, see rows with Days value {5,6,7,8} for StartDate of '20090511'.
Rows with Days value {6,7} should all be Monday 18th, right?

I am taking the @days as number of business days to add to the reference date.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 09:35:38
Ok, so it's not adding DAYS days to a date DATE and return the following business date?
You have a date, add DAYS business days, and get the following business date?

And you don't wan't to use a calendar table?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 09:48:32
of-course for actual implementation i would have used a calendar table with will be able to cater for Sat, Sun and other holidays as well easily.

This is one of those question that has been asked several times in the forum and it has been circulating in my head for quite sometime. It's one of those Monday, whatever may goes wrong will goes wrong all in one day. Need to de-stress a bit.

Now i can have a good night sleep.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 10:36:13
Try this "cheat"...
It will work for both calculating forward and backward.

However this gives different result when starting on an weekend.
What is 0 business days added to a saturday or sunday?
CREATE FUNCTION dbo.fnAddBusinessDays
(
@Date DATETIME,
@BusinessDays SMALLINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Days SMALLINT

SELECT @Days = ABS(@BusinessDays),
@BusinessDays = COALESCE(NULLIF(@BusinessDays, 0), 1)

WHILE @Days > 0 OR DATEDIFF(DAY, -53690, @Date) % 7 / 5 = 1
SELECT @Date = DATEADD(DAY, SIGN(@BusinessDays), @Date),
@Days = @Days + DATEDIFF(DAY, -53690, @Date) % 7 / 5 - 1

RETURN @Date
END


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 21:03:56
quote:
However this gives different result when starting on an weekend.
What is 0 business days added to a saturday or sunday?

I was thinking about this also. Weekend could be treated as Friday, so adding 1 business day to Fri / Sat / Sun gives you Mon


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -