<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Alexander Kuznetsov...
»
Fun with business days, calendar tables, and...
31 posts, Page 1 of 4
1
2
3
4
»
»»
Fun with business days, calendar tables, and test-driven development
Rate Topic
Display Mode
Topic Options
Author
Message
Alexander Kuznetsov
Alexander Kuznetsov
Posted Wednesday, September 22, 2010 8:25 PM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:55 AM
Points: 219,
Visits: 807
Comments posted to this topic are about the item
Fun with business days, calendar tables, and test-driven development
Post #991677
RichB
RichB
Posted Thursday, September 23, 2010 1:21 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:24 AM
Points: 1,028,
Visits: 759
Hi
Just a suggestion - but when you are doing your workday bit - you should really ensure you get the right setting for datefirst into the script, otherwise you might find it going horribly wrong...
Post #991771
feakesj
feakesj
Posted Thursday, September 23, 2010 1:32 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, October 19, 2010 3:12 AM
Points: 3,
Visits: 17
The article tries to cover too much ground and ends up doing none of the topics justice. In particular, the first function described would have been much better implemented as an additional column on the calendar table -- a key benefit of such tables is that they allow results to be looked up rather than calculated.
Post #991775
Iulian -207023
Iulian -207023
Posted Thursday, September 23, 2010 2:36 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, June 13, 2013 6:47 AM
Points: 847,
Visits: 770
Very nice article Alex, thank you.
Indeed IsBusinessDate 'Y' or 'N' is great for building an understanding of the concepts, I remember when I implemented this functionality using something similar; I used the name of the holiday in the HolidayName field ('Sunday', 'Saturday', 'Easter', 'Independence day', etc. ) and considering as business day the records where HolidayName is null or empty.
This helped me when I was asked
"why there are less working days this month?"
, and I had the answer at hand. Also it was easy for the HR to define company's holidays as they might change during the year, by just typing the name of the holiday in the interface.
I am curious in approaching multiple calendars, i.e. for different departments or for different geographies.
For example: National days are different for most of the countries but also some religious holidays, Italian branch of one company has the Easter holiday in another period of time than Russian branch.
Also for production department some Saturdays might be business/working days since for sales dept. Saturdays might be weekends/non business days.
Kind regards,
Iulian Cozma
Post #991810
wildh
wildh
Posted Thursday, September 23, 2010 3:09 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 7:08 AM
Points: 292,
Visits: 657
I'd agree with feakesj, we've got an 'is working day' bit field. Work out the days by counting that value between the two dates, simple. Looks this is an over engineered solution. However, I will admit that this solution dosnt work well if multiple sites/ business units have different holidays, but then again neither would the articles solution.
Post #991829
Andrew Notarian
Andrew Notarian
Posted Thursday, September 23, 2010 6:51 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, January 28, 2013 12:50 PM
Points: 26,
Visits: 212
Someone should really make a list of US Federal Holidays. You would think that data.gov would have something like that.
As many have done before me, I ended up making my own table. None of the solutions I found online addressed the move of fixed-date holidays to prior Fridays or following Mondays. And most of them had Groundhog Day in there like that actually means anything outside Punxsutawney.
Post #991971
wildh
wildh
Posted Thursday, September 23, 2010 8:00 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 7:08 AM
Points: 292,
Visits: 657
Andrew,
Is this any good for you? - http://www.opm.gov/operating_status_schedules/fedhol/2010.asp
btw, Groundhog Day was on TV this weeend in the UK, hadn't seen it in years :)
Post #992034
Andrew Notarian
Andrew Notarian
Posted Thursday, September 23, 2010 8:11 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, January 28, 2013 12:50 PM
Points: 26,
Visits: 212
wildh (9/23/2010)
Andrew,
Is this any good for you? - http://www.opm.gov/operating_status_schedules/fedhol/2010.asp
btw, Groundhog Day was on TV this weeend in the UK, hadn't seen it in years :)
This is the master list as provided by the US government, but it's not very easy to parse.
Someone needs to make this list into something clean like XML, JSON, etc. Hell, even a CSV would be better than parsing 10 web pages with weekday names and optional asterisks.
Post #992052
wildh
wildh
Posted Thursday, September 23, 2010 8:29 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 7:08 AM
Points: 292,
Visits: 657
Is this any good - http://facility9.com/2009/04/23/populating-us-federal-holidays-in-a-calendar-table/
or -
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
We have a similar issue in the UK but it's only a 10 min job to crib it from a web site into a update script. But hang on, then it's a 4 month wait for the DBA's to run it.
Post #992069
Robert J
Robert J
Posted Thursday, September 23, 2010 8:52 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:34 PM
Points: 31,
Visits: 59
Many times I have had to ask what portion of this date range was non-work time.
This does indeed vary by geography so we have added gmt start and end for each holiday.
We then have to handle four cases to determine the overlaps so the function has to sum the time from UNION ALL of the cases.
I can post this function if anyone is interested but if there is a better way please let me know.
Post #992093
« Prev Topic
|
Next Topic »
31 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
