Excel WORKDAY equivalent in SQL

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

Excel WORKDAY equivalent in SQL

Author

Topic

imlycett
Starting Member

1 Post

Posted - 2014-04-29 : 05:35:56

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-30 : 13:45:36

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources