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.
Author |
Topic |
kashif.special2005
Starting Member
10 Posts |
Posted - 2013-06-12 : 06:55:19
|
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.ThanksKashif |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 07:20:05
|
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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 07:37:28
|
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 listSELECT Date FROM dbo.CalendarTable('2010-01-01','2010-02-28',1,0)EXCEPTSELECT date FROM tblHoliday;For this UDF dbo.CalendarTable refer http://visakhm.blogspot.in/2010/02/generating-calendar-table.html--Chandu |
|
|
kashif.special2005
Starting Member
10 Posts |
Posted - 2013-06-12 : 08:21:38
|
Hi,Thanks for reply, please find below the example.I have a table ‘tblHoliday’ in SQL Server like below.Holiday_Date4/18/20134/22/20134/24/20134/29/2013And 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 belowExample 1:udfWorkingDays ‘04/30/2013’, -1It should return me (04/26/2013) – excluding weekends and holiday listExample 2:udfWorkingDays ‘04/30/2013’, -2It should return me (04/25/2013) – excluding weekends and holiday listExample 3:udfWorkingDays ‘04/30/2013’, -3It should return me (04/23/2013) – excluding weekends and holiday listThanksKashif |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 09:30:50
|
[code]CREATE TABLE Holiday_Date (Holiday DATE)INSERT INTO Holiday_DateSELECT '4/18/2013' union allSELECT '4/22/2013' union allSELECT '4/24/2013' union allSELECT '4/29/2013' GOCREATE FUNCTION udfWorkingDays ( @Date DATE, @PrevDayNo INT)RETURNS DATEASBEGIN 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 @PrevDayENDGODECLARE @Date DATE = '04/30/2013', @PrevDayNo INT = -3SELECT dbo.udfWorkingDays(@Date, @PrevDayNo)[/code]--Chandu |
|
|
kashif.special2005
Starting Member
10 Posts |
Posted - 2013-06-12 : 10:44:58
|
Hi,Thank you so much, it is working fineThanks once again.Kashif |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-13 : 00:26:08
|
quote: Originally posted by kashif.special2005 Hi,Thank you so much, it is working fineThanks once again.Kashif
welcome--Chandu |
|
|
kashif.special2005
Starting Member
10 Posts |
Posted - 2013-06-14 : 09:08:12
|
Hi,The function is working fine, however I want some modification in the function, likeWhen 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 datetimeset @Date=udfWorkingDays('05/30/2013',0)select * from dbo.HWDomain_Holiday_Datesselect @Date |
|
|
|
|
|
|
|