Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Calculating Work Days Expand / Collapse
Author
Message
Posted Tuesday, January 04, 2005 3:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253

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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #153606
Posted Wednesday, January 05, 2005 3:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 22, 2005 4:01 AM
Points: 118, Visits: 1

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.

Post #153683
Posted Wednesday, January 05, 2005 3:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 07, 2005 7:11 PM
Points: 1, Visits: 1

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

Post #153691
Posted Wednesday, January 05, 2005 7:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:12 AM
Points: 229, Visits: 164

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



--------------------------------
Post #153732
Posted Wednesday, January 05, 2005 7:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 03, 2013 10:38 AM
Points: 199, Visits: 55

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.




Post #153734
Posted Wednesday, January 05, 2005 9:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 17, 2005 8:13 AM
Points: 41, Visits: 1

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)

Post #153784
Posted Wednesday, January 05, 2005 12:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 03, 2013 10:38 AM
Points: 199, Visits: 55
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.


Post #153830
Posted Friday, January 07, 2005 10:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253

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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #154453
Posted Monday, January 10, 2005 1:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 07, 2014 4:06 AM
Points: 804, Visits: 175

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 ).

Post #154513
Posted Monday, January 10, 2005 3:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #154531
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse