SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 fn_next_business_day
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2009 :  07:22:54  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 08/03/2009 09:33:05

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2009 :  07:23:52  Show Profile  Reply with Quote
Peter or anybody with a more efficient way ?


KH
Time is always against us


Edited by - khtan on 08/03/2009 07:38:24
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 08/03/2009 :  09:14:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 08/03/2009 09:26:48
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2009 :  09:26:54  Show Profile  Reply with Quote
thanks for the feedback. I will take a look at it


KH
Time is always against us

Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

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

--------------------
Rock n Roll with SQL

Edited by - rocknpop on 08/03/2009 09:45:39
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2009 :  09:32:12  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 08/03/2009 :  09:35:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Singapore
17601 Posts

Posted - 08/03/2009 :  09:48:32  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 08/03/2009 :  10:36:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 08/03/2009 11:01:38
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2009 :  21:03:56  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000