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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL SERVER return a date through a UDF
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kashif.special2005
Starting Member

India
10 Posts

Posted - 06/12/2013 :  06:55:19  Show Profile  Reply with Quote
Hi,

My name is Kashif, I am very new in sql server, I want create a UDF function that will return a previous date (exclude saturday, sunday and holiday)

Holiday's list in a table called 'tblHoliday'

Please help me to get desired result.

Thanks
Kashif

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/12/2013 :  07:20:05  Show Profile  Reply with Quote
Can you elaborate the requirement?
Do want to Next Working day for a given input date?
List of dates in a month/year without weekends and holidays?

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/12/2013 :  07:37:28  Show Profile  Reply with Quote
SELECT Date,Day,WeekDay FROM dbo.CalendarTable('2010-01-01','2010-02-28',1,0) -- give you the dates from 1st jan 2010 to 28th Feb 2010 without weekends....
--To exclude hoilidays list
SELECT Date FROM dbo.CalendarTable('2010-01-01','2010-02-28',1,0)
EXCEPT
SELECT date FROM tblHoliday;

For this UDF dbo.CalendarTable refer http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

--
Chandu
Go to Top of Page

kashif.special2005
Starting Member

India
10 Posts

Posted - 06/12/2013 :  08:21:38  Show Profile  Reply with Quote
Hi,

Thanks for reply, please find below the example.

I have a table ‘tblHoliday’ in SQL Server like below.

Holiday_Date
4/18/2013
4/22/2013
4/24/2013
4/29/2013

And I want to create a UDF function that will give me previous business days excluding (Saturday, Sunday And Holiday).

Example,

Suppose I have created a UDF called ‘udfWorkingDays’, when I will execute it like below

Example 1:

udfWorkingDays ‘04/30/2013’, -1

It should return me (04/26/2013) – excluding weekends and holiday list

Example 2:

udfWorkingDays ‘04/30/2013’, -2

It should return me (04/25/2013) – excluding weekends and holiday list

Example 3:

udfWorkingDays ‘04/30/2013’, -3

It should return me (04/23/2013) – excluding weekends and holiday list

Thanks
Kashif
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/12/2013 :  09:30:50  Show Profile  Reply with Quote
CREATE TABLE Holiday_Date (Holiday DATE)
INSERT INTO Holiday_Date
SELECT '4/18/2013' union all
SELECT '4/22/2013' union all
SELECT '4/24/2013' union all
SELECT '4/29/2013' 

GO
CREATE FUNCTION udfWorkingDays ( @Date DATE, @PrevDayNo INT)
RETURNS DATE
AS
BEGIN
	DECLARE @PrevDay DATE; 
	;WITH CTE(PreviousDay) AS 
	(
		SELECT @Date
		UNION ALL
		SELECT DATEADD ( DD, -1, PreviousDay)
		FROM CTE 
		WHERE DATEDIFF( DD, PreviousDay, @Date) <=365
	)
	SELECT @PrevDay = PreviousDay
	FROM (	
	SELECT ROW_NUMBER() OVER(ORDER BY PreviousDay DESC) RN, PreviousDay
			FROM CTE
			WHERE DATENAME(WEEKDAY, PreviousDay) NOT IN ('Saturday', 'Sunday')
				AND PreviousDay NOT IN (SELECT Holiday FROM Holiday_Date)
				AND PreviousDay <= DATEADD(DD, -1, @Date)
		) Temp
	WHERE RN = ABS(@PrevDayNo)
	OPTION (MAXRECURSION 0)
	
	RETURN @PrevDay
END
GO

DECLARE @Date DATE = '04/30/2013', @PrevDayNo INT = -3
SELECT dbo.udfWorkingDays(@Date, @PrevDayNo)


--
Chandu

Edited by - bandi on 06/12/2013 09:44:56
Go to Top of Page

kashif.special2005
Starting Member

India
10 Posts

Posted - 06/12/2013 :  10:44:58  Show Profile  Reply with Quote
Hi,

Thank you so much, it is working fine

Thanks once again.
Kashif
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/13/2013 :  00:26:08  Show Profile  Reply with Quote
quote:
Originally posted by kashif.special2005

Hi,

Thank you so much, it is working fine

Thanks once again.
Kashif


welcome

--
Chandu
Go to Top of Page

kashif.special2005
Starting Member

India
10 Posts

Posted - 06/14/2013 :  09:08:12  Show Profile  Reply with Quote
Hi,

The function is working fine, however I want some modification in the function, like

When I am passing 0 instead of -1 it is giving me NULL value, however I want that If I pass 0 as a parameter it should return passing date, if the passing date is not (saturday, sunday and holiday).

Example:

when I execute below query it is giving me NULL value, however it should return pass date that is '05/30/2013' (execluding saturday, sunday and holiday)

DECLARE @Date datetime

set @Date=udfWorkingDays('05/30/2013',0)

select * from dbo.HWDomain_Holiday_Dates
select @Date
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.08 seconds. Powered By: Snitz Forums 2000