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 Monday, January 10, 2005 6:16 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955

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

(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 #154547
Posted Monday, January 10, 2005 8:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 7:44 AM
Points: 263, Visits: 44

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.

Post #154592
Posted Monday, January 10, 2005 9:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 25, 2006 2:50 PM
Points: 16, Visits: 1

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

 

Post #154617
Posted Monday, January 10, 2005 11:30 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030

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
Post #154652
Posted Monday, January 10, 2005 1:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #154664
Posted Monday, January 10, 2005 5:43 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955

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.



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

(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 #154693
Posted Tuesday, January 11, 2005 8:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
Jeff:

Personally I would include ALL days in the calendar table -- that way you can easily detect gaps, count days minus holidays in ranges, etc.

The calendar table can't get too big, either -- 365 days / year means that 10 years of coverage is only 3650 rows.

Here's a good article on the subject that also includes a basic script to create one:

http://www.aspfaq.com/show.asp?id=2519

Thanks for starting this thread with your article -- I agree, it was very enlightening!


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #154791
Posted Tuesday, January 11, 2005 8:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:14 AM
Points: 612, Visits: 47
Jeff!

May I contribute my mite?

We may also consider the function

datediff(wk, @startdate, @enddate)

to calculate Sundays between @startdate and @enddate, where end date is included in the time interval but start date is not.
So, if we want to calculate Sundays in the “closed” interval we should use:

datediff(wk, @startdate - 1, @enddate).

If we want to calculate Saturdays, we should turn Saturdays into Sundays by shifting the interval one day forward:

datediff(wk, @startdate, @enddate + 1).

Finally, we can calculate the total number of workdays in the interval by subtracting Sundays and Saturdays from the total number of days:

datediff(dd, @startdate, @enddate + 1)
- datediff(wk, @startdate - 1, @enddate) - datediff(wk, @startdate, @enddate + 1)

Such a variant is language-independent and by my calculations approximately 10% faster (on my computer).

Best regards, Oleg.



Post #154800
Posted Tuesday, January 11, 2005 6:03 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955

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!



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

(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 #154924
Posted Saturday, July 23, 2005 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2008 12:47 AM
Points: 1, Visits: 3

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

Post #203754
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse