Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating Work Days Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, January 10, 2005 6:16 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:38 AM Points: 42,077, Visits: 39,464
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #154547
 Posted Monday, January 10, 2005 8:14 AM
 SSC 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=110259You 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 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 Group: General Forum Members Last Login: Wednesday, October 19, 2016 9:27 AM Points: 6,262, Visits: 2,048
 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 Group: General Forum Members Last Login: Sunday, May 3, 2015 2:03 PM Points: 1,113, Visits: 713
 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 MachanicSQL Server MVPSQLblog.com: THE SQL Server Blog Spot on the Web
Post #154664
 Posted Monday, January 10, 2005 5:43 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:38 AM Points: 42,077, Visits: 39,464
Post #154693
 Posted Tuesday, January 11, 2005 8:14 AM
 Ten Centuries Group: General Forum Members Last Login: Sunday, May 3, 2015 2:03 PM Points: 1,113, Visits: 713
 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=2519Thanks for starting this thread with your article -- I agree, it was very enlightening! --Adam MachanicSQL Server MVPSQLblog.com: THE SQL Server Blog Spot on the Web
Post #154791
 Posted Tuesday, January 11, 2005 8:37 AM
 Right there with Babe Group: General Forum Members Last Login: Sunday, August 21, 2016 7:32 AM Points: 714, Visits: 65
 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-Forever Group: General Forum Members Last Login: Today @ 9:38 AM Points: 42,077, Visits: 39,464