Calculating Work Days
-
I have used a "holidays" table with great success. In it I insert a row for each non-work day (including Saturday
and Sunday, if applicable).
This approach is very flexible, as non-work days are table-driven (and not code-driven). This increases both
simplicity and flexibility quite a bit: "exceptions" really don't exist. It is reasonably fast, simple to understand, and
hard to make an error.
The main pieces in this solution are: 1) a table tblSysHolidays, 2) a UDF named udfHolidayDays
to return the number of holidays between a StartDate and EndDate (inclusive), and
3) a UDF named udfAddBusinessDays to add or subtract N business days to/from a date--like DATEADD,
but for business days.
I usually calculate elapsed working days as:
SELECT
DATEDIFF(d, @StartDate, @EndDate) –
dbo.udfHolidayDays(@StartDate, @EndDate)
Of course I could have a UDF do this calculation, but I find it handy having the actual number of
holiday days handy (for date arithmetic, etc.)
There are other benefits of using a holiday table too. For example:
a) It is easy to select rows that have a holiday included in a date range.
b) it is fairly simple to extend to support multiple definitions of work days
(for different regions or different classes of employees): simply add a Region column to tblSysHolidays, and
modify the UDF's to allow the Region to be passed in when calling the UDF's
c) It helps "future-proof" applications: what if the company changed from a 5-day
to a 6-day work week in the future, and you had to work with both historical and current data? This is trivial and <br>seamless with a holiday table.
//=======================================
// Definition of tblSysHolidays
//=======================================
CREATE TABLE dbo.tblSysHolidays(
SysHolidayID int IDENTITY(1,1) NOT NULL,
HolidayDate datetime NOT NULL,
Description varchar(40) NULL,
CONSTRAINT [pkSysHolidays] PRIMARY KEY CLUSTERED
(HolidayDate ASC) WITH
(PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
//=======================================
// Definition of udfHolidayDays
//=======================================
CREATE FUNCTION [dbo].[udfHolidayDays](
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Result INT
DECLARE @NegSpan BIT
IF @StartDate > @EndDate SET @NegSpan = 1
ELSE SET @NegSpan = 0
SELECT @Result = COUNT(SysHolidayID)
FROM dbo.tblSysHolidays AS a
WHERE
((@NegSpan = 0) AND ( a.HolidayDate >= @StartDate AND a.HolidayDate <= @EndDate)) OR
((@NegSpan = 1) AND ( a.HolidayDate <= @StartDate AND a.HolidayDate >= @EndDate))
IF @NegSpan = 1 SET @Result = @Result * -1
RETURN @Result
END
//=======================================
// Definition of udfAddBuisnessDays
//=======================================
CREATE FUNCTION dbo.addBusinessDays
(@OrigDate datetime,
@DaysToAdd int)
RETURNS datetime
AS
BEGIN
--Note: @DaysToAdd can be positive or negative
--Note: when @DaysToAdd = 0, the function returns @OrigDate
--if it is not a holiday. If @OrigDate is a holiday, the function returns
--the next business day.
DECLARE @Return datetime
DECLARE @OrigHolidayDays int
DECLARE @FinalHolidayDays int
SET @OrigHolidayDays = dbo.holidayDays(@OrigDate, @OrigDate + @DaysToAdd)
SET @FinalHolidayDays = dbo.holidayDays(@OrigDate, @OrigDate + @DaysToAdd + @OrigHolidayDays)
WHILE @OrigHolidayDays <> @FinalHolidayDays BEGIN
SET @OrigHolidayDays = @FinalHolidayDays
SET @FinalHolidayDays = dbo.holidayDays(@OrigDate, @OrigDate + @DaysToAdd + @FinalHolidayDays)
END
SET @Return = @OrigDate + @DaysToAdd + @FinalHolidayDays
RETURN @Return
END
David Rueter
-
This is an interesting column. I've always wondered why SQL Server dosn't have an optimised built-in function to handle working days.
Holidays are very important - in the UK I can't see the point of having a working day function without taking account of holidays. As for contractors - I've yet to meet a contractor who dosn't take holidays!
Also for project management / order processing etc it is often required to work backwards from a future date. I'm not sure if this has been covered. It's not quite as straightforward as simply doing a negative DATEDIFF. We wrote the following UDF to take account of this. I think we might be able to optimise it based on the UDF described in the article - because it now looks complicated in comparison!
CREATE FUNCTION [dbo].[fn_EN_GetWorkingDate] (@Start SMALLDATETIME, @Length INT, @UseWorkingDays BIT) RETURNS SMALLDATETIME AS
BEGIN
DECLARE
@Holidays INT
DECLARE @Temp INT
DECLARE @EndDate SMALLDATETIME
DECLARE @Mod INT
DECLARE @Result SMALLDATETIME
IF
(@Length = 0)
SET @Result = @Start
ELSE
BEGIN
IF (@Length < 0)
SELECT @Mod = -1
ELSE
SELECT @Mod = 1
IF @UseWorkingDays = 1
BEGIN
--Calc number of days to Friday
--Calc makes sure we start the calculated week on Monday
SET @Temp = (5 - (((DATEPART(WEEKDAY, @Start) + @@DATEFIRST)%7)-1)) * @Mod
IF @Temp < 0
SET @Length = @Length + (2*@Mod)
IF @Length > @Temp
BEGIN
SET @EndDate = DATEADD(DAY, @Temp,@Start)
SET @Temp = @Length - @Temp
SET @EndDate = DATEADD(WEEK, @Temp/5, @EndDate)
IF (@Temp%5) > 0
BEGIN
SET @EndDate = DATEADD(DAY, @Temp%5 + 2, @EndDate)
END
SET @Length = DATEDIFF(DAY, @Start, @EndDate)
END
SELECT @Holidays = ISNULL(COUNT(*),0) FROM t_EN_BankHolidays WHERE
dtHoliday > @Start AND dtHoliday <= DATEADD(Day,@Length, @Start)
IF @Holidays > 0
BEGIN
SET @EndDate = dbo.fn_EN_GetWorkingDate (DATEADD(Day,@Length, @Start), Holidays, @UseWorkingDays)
SET @Length = DATEDIFF(DAY, @Start, @EndDate)
END
SELECT @Length = @Length + (
CASE DATENAME(weekday,DATEADD(Day,@Length,@Start))
WHEN 'Saturday' THEN 2
WHEN 'Sunday' THEN 1
ELSE 0
END) * @Mod
END
SET @Result = DATEADD(DAY, @Length, @Start)
END
RETURN @Result
END
-
I'll admit that I haven't read all the posts or even the article itself to any degree but I just thought, when I saw the heading, what I always think when I see similar articles....
Calculating the work days is fine (without holidays its a little limited but...) but 99% of the time I want a function like this its because I want to know when to start a search from. The number or workdays isn't eoungh for that.
Maybe I'm not explaining very well so....consider an app that needs to average/sum/etc. all the closing balances in a bank account in the past n days. Weekends and bank holidays must be excluded or they will mess up the totals. I basically want to know what date to start the search on....
select...from...where TableDate between ?? and @searchDate
Know what I mean?
Anyway, keep up all the good work and the sharing of your ideas and implementations.
-
Hmm
check:
Date and Time User-Defined Functions
Alexander Chigrik
GetWorkingDays UDF - maybe less comments
-
Hi,
It looks great at first, but for some reason I have a strange problem when using it.
I have a start date of Wed 1st May 2002 and an end date of Fri 31st May 2002 which is 31 days less 4 weekends so I was expecting a result of 23 week days. However, it returned 21 as the wk part of the function was returning 5 weekends.
If I try ths function between dates Fri 3rd May 2002 and Fri 31st May 2002 it now correctly counts 4 weekends and gives 21 week days as the result.
I thought the problem might lie with the value of datefirst in the database but when I run the statement SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today' it returns 1st day = 7 (i.e. Sunday, the US English default) and Today = 6 (i.e. Friday). So, that must not be the problem, although irrespective of the value of datefirst I think the function should calculate properly.
Further to this, DATEPART (wk, @STARTDATE) for Wed 1st May 2002 returns a value of 18 and DATEPART(wk, @ENDDATE) for Fri 31st May 2002 returns a value of 23, hence this is where it is getting 5 weekends from. Strangely, the value of wk changes to 19 when startdate reaches Fri 3rd May 2002 and the value of wk drops to 22 when enddate comes down to Thu 30th May 2002.
Hence, once can deduce from this that the wk cutover appears to be on a Friday.
Anyone got any ideas what's going on here and how to resolve it?
-
Eugene, that's a good collection of function you have - very useful. I suppose, in retrospect, its always the holidays that mess things up. Now, I'm not at all suggesting it cannot be done easily enough just that I have never figured a simple or quick implementation - usually there is some kind of date-walking-loop involved....kind of like....
..calculate start date
..calculate number of holidays between that and enddate
..remove
but
..have to check again if this new start date is weekend
....if it is then remove 1 or 2 days and check if this new new date is holiday
etc.
-
If holidays don't exist for you, chances are you work on weekends too, so you might as well use DATEDIFF. But if you are a contractor, you most likely bill by the hour and work different hours each day, so you have no use for either function anyway.
-
jh72i,
Here is the way I would select rows within a range of N business days:
// Date range (5 business days prior to @searchDate through @searchDate)
SET @searchInterval = -5
SELECT xxx FROM yyy
WHERE
yyy.TableDate <= @searchDate AND
yyy.TableDate >= dbo.udfAddBusinessDays(@searchDate, @searchInterval)
(@searchInterval can be either a positive or a negative number of business days.)
Here is the way I would calculate a per-business-day average for a date range:
SELECT SUM(yyy.Sales) /
(DATEDIFF(d, @startDate, @endDate) - dbo.udfHolidayDays(@startDate, @endDate))
FROM yyy
WHERE
yyy.TableDate >= @startDate AND
yyy.TableDate <= @endDate
Note that in these examples I am assuming that these dates do not contain a time portion.
David Rueter
-
Accountants I work with use a business day concept that does not follow the calendar. For example, our first business day for this month was 9/4 (Tuesday). August business included 9/3 (Labor Day) and the Saturday and Sunday preceeding it.
-
Arrrgh... And to think I spent half a day writing a sketchy, cumbersome client-side loop to get this exact value for an insert statment... *sigh*
-
Dizzy...
I see no similar problem... it operates as expected for the dates you posted having problems with...
-- Wed 1st May 2002 and an end date of Fri 31st May 2002 (Should be 23 days)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '20020501'
SET @EndDate = '20020531'
SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
And, it doesn't matter what DateFirst is set to. Please post the code that presented the problem for you so I can see what's going on...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Actually, that's quite a nice compliment...
thanks 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Wasn't meant to resolve business days per month nor figure out what a business month consists of... it was meant to find weekdays (despite the title). If you don't need it, don't use it
Use a calendar table, instead.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Totally wrong approach?
Heh, extraordinary claims require extraordinary proof, Joe. If you're talking about the code I submitted and if you think a calendar table would be faster, simply submit the proof in the form of code A million rows would be acceptable for the performance test... 
As for the holidays, yes, a holiday table would be necessary... but you don't need a whole calendar table to use the code I presented.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Don't need a "date walking loop". Just use the formula I posted to calculate the week days and hit a holiday table for the number of days to subtract
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 46 through 60 (of 156 total)
You must be logged in to reply to this topic. Login to reply