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
 Excel WORKDAY equivalent in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

imlycett
Starting Member

1 Posts

Posted - 04/29/2014 :  05:35:56  Show Profile  Reply with Quote
I have designed a SQL Function similar to Excel WORKDAY that calculates an end-date by counting on a specified number of working days. It allows the specification of a table of custom non-working days. I'm not clear on the performance implications of using a recursive common table expression in this way.

After a quick Google search, I found many options that counted non-weekends but I didn't turn up any solutions that offer a custom calendar. If anyone can suggest improvements, or even point me at how I might improve the efficiency, it would be great to hear them.

CREATE FUNCTION AddWorkingDays
(
@StartDate Date,
@NumberOfWorkingDays INT,
@CalendarToUse INT
)
RETURNS Date
AS
BEGIN
-- Declare the return variable here
DECLARE @EndDate as Date

;WITH CTE_Days AS
(
SELECT
0 AS RowNum
,@StartDate AS [DayToCheck]
,CASE DATEPART("WEEKDAY", @StartDate)
WHEN 1 THEN 1
WHEN 7 THEN 1
ELSE 0
END AS IsWeekend
,CASE WHEN EXISTS(SELECT [Date] FROM dbo.NonWorkingDays WHERE CalendarID = @CalendarToUse AND [Date] = @StartDate) THEN 1 ELSE 0 END AS IsPublicHoliday

UNION ALL

SELECT
CASE
WHEN
DATEPART("WEEKDAY", DATEADD("d", 1, [DayToCheck])) = 1
OR
DATEPART("WEEKDAY", DATEADD("d", 1, [DayToCheck])) = 7
OR
EXISTS(
SELECT [Date] FROM dbo.NonWorkingDays WHERE CalendarID = @CalendarToUse AND [Date] = DATEADD("d", 1, [DayToCheck])
)
THEN [RowNum] ELSE [RowNum] + 1 END AS RowNum
,DATEADD("d", 1, [DayToCheck])
,CASE DATEPART("WEEKDAY", DATEADD("d", 1, [DayToCheck]))
WHEN 1 THEN 1
WHEN 7 THEN 1
ELSE 0
END AS IsWeekend
,CASE WHEN EXISTS(SELECT [Date] FROM dbo.NonWorkingDays WHERE CalendarID = @CalendarToUse AND [Date] = DATEADD("d", 1, [DayToCheck])) THEN 1 ELSE 0 END AS IsPublicHoliday
FROM CTE_Days
WHERE [RowNum] < @NumberOfWorkingDays
)
SELECT @EndDate = MAX([DayToCheck])
FROM CTE_Days

-- Return the result of the function
RETURN @EndDate

END
GO



Edited by - imlycett on 04/29/2014 05:36:59

gbritton
Aged Yak Warrior

854 Posts

Posted - 04/30/2014 :  13:45:36  Show Profile  Reply with Quote
One approach sometimes used in a Data Warehouse setting is to have a Date table that at a minimum has these columns:

Date(int), Year(smallint), Month(tinyint), Day(tinyint), MonthName(varchar(9)), DayName(varchar(9)), FullDate (varchar(20)), IsWorkday (bit), IsWeekend (bit), IsStatuatoryHoliday (bit)

A typical row might be:

(20140430, 2014, 4, 30, 'April', 'Wednesday', 'April 30, 2014' 1, 0, 0)

The table would have a PK clustered index on the Date column. The business would populate this table in advance, usually several years in advance and in arrears, with all proper statutory holidays flagged as such. Using such a table, it is easy to join it with other tables. The first column can be used as an FK in referring tables, saving space in those tables and having ready access to tests for workday, weekend, holiday etc. You can add any columns to this table that is meaningful for your business. Usually the resulting table is not too big (maybe a million bytes or two)

Doing it this way you never have to call functions in your queries to make these sorts of tests.
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.07 seconds. Powered By: Snitz Forums 2000