Calculating Work Days
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp
Hey folks... I'm not sure why they decided to repost this old article, but before you make any comments, please realize this...
The name of the article should have been "Calculating WEEKDAYS in the U.S.A".
--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) -
While there is no questioning your function's speed or elegance, for me your first 'Note' considerably reduces the usefulness. I refer to:
Notes: 1. Holidays are NOT considered.
Here in England we have eight (if I have counted right) public ('bank') holidays per year, which are weekdays that explicitly do not count as working days for things like cheque clearance. For example, 25th and 26th December (or the weekdays following them, if the 25th and/or the 26th are on a weekend) are public holidays, so one can say with certainty that December has two fewer working days than a simple 'days-weekenddays' would predict. The two public holidays associated with Easter are even more trouble.
Now, one might say that 8 days out of about 261 is nothing to worry about; but that would be against the spirit of the problem, and in any case, if that approximation were acceptable, you might as well use num_days*5/7.
Or you might choose to use a modification of the supplied algorithm, to the effect of num_workdays = num_days - 2*num_weeks - num_jan1sts - num_goodfridays - num_eastermondays...
But that is deeply cumbersome, and in any event will be wrong as soon as you change locale.
For these reasons it is my opinion that the only really practical solution is what you refer to as a "date table". I freely admit that this method does as you say involve "the creation and yearly maintenance of what usually turn out to be some rather lengthy but still finite date or sequence tables". However, if we want correctness we have to be prepared to work for it - the vagaries of our calendar and culture are such that there doesn't necessarily *have* to "be a better way"!
On the plus side, once we have accepted the need for a date table, look what we gain:
- Simple changes to the date table enable us to change locale in an instant, or we could easily have a multi-locale model
- A very little work gives us role-based date tables, for example for workers who have shift patterns such as '10 days from 14' in a non-predictable pattern
- Another little bit more work enables us to categorize dates more finely than 'workday'/'non-workday', leading no doubt to all sorts of useful reports
Once again I have nothing against your function, which does a great job of working with all the quirks of the T-SQL date functions. I'm just offering my experience that a real-world solution can't afford to not consider holidays, and for this reason a date table is the way to go.
-
For the number of us (greater everyday) who live in "contractor-land," where holidays don't exist, this is a very neat function.
I'm very pleased by it for two reasons...
- Reminds developers of the existence/usefulness/potential of UDFs in the data-layer
- Does so in a way that never even hints at cursor use
Now, to come up with the platform-specific equivalents...I work in an Oracle/Informix shop
-
Hi
I Feel the function dont serve the purpose. First thing is in INDIA we dont have 5 Day Working Concept except for Software Comapnies. ALL are 6 Days Working and what about the mandatory Holidays in a Region Like Republic Day and Independance Day In India , Like that each region will have its own Holidays. And if the startdate and and Enddate is in Between a Week how the function is making sure that the No Of Sundays and Saturdays in the first and last fractions ?. I feel in Real Life this function will fail. In real Life you need to handle the following cases
1. Any Type of Work Week Definition Based on Diffrent REgion and Locations
Eg. The Work Days are diffrent in US,INDIA and UAE
2. Holidays and Mandatory Holidays in Diffrernt Locations
Eg. In INDIA Jan 26, Aug 15, OCt 2 is a Mandatory Holidays. But there can be other holidays which is Non Mandatory .
I feel untill you dont handle this issues this cannot be used in a Live Situation. I have nothing against your UDF . But i Feel this need to be generalised. to be used in real Life Situations
Best
Jeswanth
--------------------------------
-
Actually, if you are willing to use a "Date Table," make it a date dimension table. Do all of the date calculations that can be done to a date ahead of time, calculate the day of week, week of year, day of month, month name and number, etc. You can add a column for something like "HolidayFlag" and another one for "WeekendFlag." Now, all you have to do is go to this table with the beginning and ending dates and count the number of No's in the weekend flag and subtract the number of Yes's in the Holiday flag for the date range you want to check.
If you are interested, I can post the structure of such a table.
-
Create a table (Holiday) with two columns, and enter holiday dates and country where they are observed. Both columns should be the Clustered PK. If the same holiday is observed in several countries, add a record listing date and (each) country where it is observed.
To find workdays, first exclude weekends (several options are available), and deduct amount of holidays (DECLARE @FirstDate DATETIME, @LastDate DATETIME, @Country VARCHAR (25)
SELECT COUNT(*) FROM Holiday WHERE data BETWEEN @FirstDate AND @LastDate and country = @Country)
-
Sorry Shie, but country is really not an issue. In dealing with companies, their holidays are different between them even when they are in the same country. If you want to do it correctly, the Date table would be independent of the country/company. The country/company would be it's own table. The holiday name would be it's own table. And, finally, the intersection table of the holiday, company/country, and the date itself. Using a unique index on the combination and a surrogate key (identity column) for the primary key. Enterprise solution VS not.
-
Great responses, all, folks. Thank you very much for taking the time to make them.
Hmmmm… perhaps the title of the article should have been “Calculating ‘Week’ Days” instead of calculating “Work” days…
The function in the article wasn’t meant to be an all serving world wide compatible universal function. Like any of Microsoft’s date functions
, it was meant to do just one thing… solve for the number of Mon-Fri days (normal workdays in the U.S.) in a date range, not including Holidays, without any limits other than the limit Microsoft imposed with their maximum date of the DateTime data-type. It was meant to do it without the use of cursors, WHILE loops, counters, tables that have 1 one row for each day of the year for several years, or tables that required one row for each weekend day for several years. It wasn’t meant to tell you the day of the week, what day of the month a date is, or any of the other date functions that are readily available in SQL Server.That being said, let me see if I can answer a couple of your concerns…
About Holidays:
I agree that holidays are an important part of planning. As difficult as it would be to design an electronic universal translator that could translate from or to any language or dialect in the world, so it is with Holidays. As some have pointed out, there are bank holidays for processing, holidays that only certain states in the U.S. take (Rhode Island gets Patriots day off, most other states don’t even know what Patriots day is), company holidays (we get Christmas Eve off, lot’s of companies don’t), national holidays which vary greatly by country, etc, etc. The function simply wasn’t meant to solve for every possible holiday for every company in every country. Like I said, I should have titled the article “Calculating ‘Week’ Days”.
One of the many problems I’ve seen with many methods of calculating “work days” has been the creation of some very large but still limited tables usually consisting of one row for each day of the year for several years. If you consider the function I wrote as a simple function that does one thing well and combine it with a very simple easy to maintain “Holiday” table (<20 rows per year as opposed to 104+ to 365 per year?), you would indeed be able to account for any holiday. In other words, a holiday table combined with the use of this function would eliminate the need to list weekends in the table.
Another way of putting it is that this function successfully excludes all weekend days. One of the respondents suggested there are many methods available for doing this but it is those methods that I took exception to because of their poor performance or the maintenance of “date dimension” tables that had to account for weekends as well as holidays.
One of the respondents to this article correctly suggested that if you could exclude weekends, which this function does, then the holiday table would only contain holidays and a simple SELECT statement could be used to exclude the holidays, as well. That would be a very easy add-in to the function and all are encouraged to do that to meet the particular needs of their particular country and company. The add-in to the RESULT of the function might look something like this…
-- Subtract 1 day for each holiday in the date range
-(SELECT COUNT(*)
FROM Holidays
WHERE HolidayDate >= @StartDate
AND HolidayDate <= @EndDate
Yup, I know about BETWEEN… that would be the subject of whole different article.
6 Day WorkWeeks
Being in IT, I’d kill to only work six days a week
but I get your point. As previously stated, the function was designed to do only one thing well and, depending on your point of view, it either counts all of the weekdays or it excludes all of the weekend days. Since a six day work week in India and other countries usually means the workdays are Mon-Sat, a simple modification to the function could be made to the “meat” of the function to calculate 6 day work weeks (or exclude 1 day weekends, whichever) as follows:--===== Calculate and return the number of workdays using the
-- input parameters. This is the meat of the function.
-- This is really just one formula with a couple of parts
-- that are listed on separate lines for documentation
-- purposes.
-- Modified to handle 6 day Mon-Sat workdays
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 1 day for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)) -- Times 2 removed
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-- Whole statement removed
Of course, if you need both, you could either have two nearly identical functions or you could add a parameter to identify whether to calculate a five day or a six day work week using CASE statements.
Again, let me stress that the function was not meant to be the end all of work day calculators. It was only meant to count weekdays of a 5 day workweek or to exclude weekend days without performance problems, table limits, or the maintenance of very large date tables. You can take the meat of this function and easily add whatever functionality your country and company demands.
Date Dimension Tables:
Although highly customizable, I chose not to use a Date Dimension Table because it didn’t make sense to me to store data like month name, etc, when SQL has so many high speed functions that readily return this data. And the functions are faster than the retrieval of any data from disk.
Using just a combination of this function (with the holiday mod above), a very simple holiday-only table, and the easy to use date functions readily available in SQL Server, anything you want to know about a date can be derived in a very high speed fashion without the use of large Date Dimension Tables.
As one of the respondents to this article pointed out, you could add a dimension or two to the holiday-only table and make a simple modification to the function to use it if international, interstate, or inter-company holiday calculations were necessary. This would still be a holiday-only table and that would be much smaller and easier to maintain than a table listing weekend days, holidays, and things that high speed date functions already provide.
Cursors
One of the respondents to this article pointed out that this function “Does so in a way that never even hints at cursor use.” That, my knowledgeable friend, was one of the main goals of this function and I thank you for recognizing that. Many of the methods I’ve seen for doing what this function does, use Cursors or RBAR (Row By Agonizing Row) WHILE loops. There are many articles and many more forum posts that denounce the use of cursors for any reason and I agree. While I may occasionally use a set based WHILE loop, I have never found reason to use a cursor in SQL Server for any reason (not even for Hierarchies). Sure, it sometimes takes longer to figure out a different solution (this function was certainly an example of that), but the performance gains always seem to make it all worth while.
Thank You
Thank all of you for responding. This has been a great first experience for me. Lots of ideas came out and it shows that people on this forum are always trying to think of a better way. I’ve met a lot of “cooters” in this business and none of you folks have to worry about becoming one.
Sincerely,
--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) -
Hi,
Good function. And surely, a very clear documentation. Just 1 (little) thing. As my server is case sensitive, I had to change USE MASTER into USE master, and XType into xtype. But the function itself is OK, and compatible with my case sensitive server ( and this is mostly not the case ! ) ... thank you very much. I will probably change my existing function ( using a table with work days, generated each year after encoding of the holidays ).
-
Just a note on creating user-defined objects in master.
I think this makes maintenance and disaster recovery more difficult. Since you didn't make this function a "true" system function, it might be better to have a separate DB if you have multiple objects you want to share in different databases. Now, if you want to make it a "true" system function, you need to add it to the system function schema like this:
SP_CHANGEOBJECTOWNER 'fn_WorkDays','system_function_schema'
That way you should be able to call it like any other built-in fn_* function. I don't suggest this a) for the above mentioned reason and b) I am told that SQL Server 2005 reinvents this wheel anew.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Bert,
Thanks for the great compliment! As my follow-up note (long posting above) suggests, you can easily add a reference to a simplified holiday table to the function.
Frank,
Thanks for the tip. Always a pleasure to get these from one of the best.
--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) -
For anyone in England and Wales, I've posted a function that calculates bank holidays that you may find useful. I make no claims to efficiency, but it does the job!
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=110259
You could set it up on a job to re-populate a holidays table every year or something to whatever size you need, then you've got a small holiday table that you don't have to worry about.
-
Excellent function!!
But as others have pointed out holidays will invariably need to be taken into account; we have a holiday table which (simplified version) is called HCD and has one field HolidayDate (DateTime). You simply need to add a simple select calc to the end of your functions thusly:
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
--New calc to account for Holidays added here
- (SELECT COUNT(*) FROM HCD
WHERE HolidayDate BETWEEN @StartDate AND @EndDate)
 
This should not detract from the original elegance of the solution as this is a simple select, not requiring cursors/loops
-
SELECT COUNT(*) FROM HCD
WHERE HolidayDate BETWEEN @StartDate AND @EndDate
I has been my experience that a "Calendar table" is BY FAR the most flexible solution with the minimum MAINTENANCE! The Calculation of holidays like that is Not a good thing when you have Shifts, Temp or Special Kind of employees. Besides that It does serve for multiple purposes which in my opinion DO outweight the so called "speed Difference" which again in my opinion is not that terrible and depending on what you are doing the amount of effort to accomplish some things varies widely.
Again your UDF does have a purpose but I still think that the Calendar table should be the recomendation of choice and not the other way around.
Thanks for the informative article,
HTH
* Noel -
I would always handle this with a calendar table, personally. There's a lot more than just holiday dates you can include: Comments on what the holiday is (or other comments on a per-day basis), day of week/day of year/day of quarter/etc for ease and performance of writing reporting queries, and perhaps other things I haven't thought of yet... It can be queried and updated easily by anyone who needs to (you can write an app to maintain it), and it's entirely flexible.
--
Adam Machanic
whoisactive
Viewing 15 posts - 1 through 15 (of 156 total)
You must be logged in to reply to this topic. Login to reply