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.

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

Again, lot's of good posts.  Shie, Robert L, Noeld, and Adam have all pointed out that the use of a "Calendar Table" or "Holiday Table" to hold non-workdays (I'm assuming neither of these hold M-F normal workdays except maybe when a shift differential must be accounted for, haven't quite figured that one out yet) could be used to augment this function.  I took a look at Paul Cresham's UK Bank Holiday script he posted and it certainly looks like you could use it straight up as is or you could modify it to easily populate several years worth of Holiday table.

The flexibility of Holiday tables (Calendar tables) certainly seems to have some merit although, unless you make a pretty big one, will have some limits whereas the functions are much less limited.  As with many Date/Time calculations, it sometimes takes more than one function/method to get what you want.  In a previous post on this same thread, I suggested that the article should have been called "Calculating Week Days" instead of "Calculating Work Days".  Through the intelligent use of a combination of functions like this one and Paul's, Holiday tables, and perhaps, a shift differential function or embedded calculation, you can build the "ultimate work day calculator" for your particular needs.

Again, thank you all for your feedback.  I don't know if it's true for you but I've sure learned a lot about what people need or have done concerning the true calculation of work days (not usually weekends but sometimes includes Saturdays, not holidays [we all knew that], not bank holidays, sometimes needs to account for shift differentials) especially for areas outside of the U.S.

Just a reminder... this function wasn't designed to do anything except count the number of week days in a date range much like MS-SQL's DATEEPART calculates, well, date parts.  I do appologize for the misnaming of the article.

First, Adam, thanks for the great link.  I took a look at ALL of the scripts including the additional reference to the auxiliary number table and I definitely see your point and the case for an auxiliary date table is a powerful one.  So powerful that I'll relent and admit that dedicating the necessary disk space is well worth the investment of disk space and the, apparently, very minor maintenance of adding additional years.  This is proof that even an old dog can learn new tricks!  Thanks for your patience and your posts on this thread.  A lot of other people would have lost their cool or just given up.

Just a note, I did some very rough calculations to see what would happen if the auxiliary date table they suggested is setup for 30 years. Each row takes up only 28 bytes (only 1 byte for the two BIT data types, could get 6 more bit columns for “free”).  Even if you tripled that size to account for the overhead of VarChar fields, the “Null Bitmaps” of fixed length fields, and the index, etc, that would only be 84 bytes per row or 30660 bytes of storage per year.  30 years would still be under a megabyte which is a pretty darned small table nowadays. 

I’ve gotta take back what I said about auxiliary date tables and say they’re very well worth setting up.  I’ll eat the crow later, when I've finished cooking it up with extra garlic and a side of humble pie.

Oleg, thanks for the great follow-up.  I haven't tested what you suggested but, based on your explanations and looking at the code you posted, it’ll probably work.  Considering that I’m probably going to setup my own auxiliary date table, I probably won’t get to testing your suggested alternate code.

Hey Everybody!  Thanks again for all of your responses.  They were great and I think I learned more than what I thought I was teaching.  Like I said, that's one of the reasons this forum is so great!

Thanks for the great function..but i do have further calrifications...the weekends in uae are Thursdays & fridays. I modified the code to match the weekends but get wrong results..any help on this??

the code is

RETURN (
                SELECT
              --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) = Friday
                      THEN 1
                      ELSE 0
                  END)

              --If EndDate is a Saturday, Subtract 1
               -(CASE WHEN DATENAME(dw,@EndDate) = Thursday
                      THEN 1
                      ELSE 0
                  END)
                )

when i enter @StartDate = '07/08/2005',@EndDate='07/14/2005'

I get 3..which is wrong..

waiting for urgent help...