July 22, 2010 at 5:05 pm
Thanks. 🙂
January 13, 2011 at 6:35 am
Hello,
Has there been a revision to add and/or flag if it's a business day e.g. Y or N. This equates not holidays and weekends, preferably in the US. Please send the script.
Thanks
January 13, 2011 at 7:01 am
Sorry, no. Life's been busy lately. Celebrating our first child. 🙂 It's on the "to do" list though (which seems to be getting longer each day!). 😉 I will post when there is an update though.
January 13, 2011 at 8:23 am
This is mentioned in my post Calculate U.S. Holidays at [/url]. I just added a column is_workday to Sean's existing script, then made my holiday script. Hope that helps!
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
January 13, 2011 at 8:27 am
Ah yes! I had forgotten about this script. Thanks Thordog! 🙂
January 13, 2011 at 9:04 am
Thordog, very nice addition. I added holiday_name to it.
January 13, 2011 at 9:30 am
Gitmo, cool! Sean did a great job on that table. I have programmers I show it to that still use SQL date functions against the table. :w00t: The whole point is you don't need any dateadd or datediff with this table, good grief! :crazy:
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
January 13, 2011 at 9:45 am
LOL! I've seen the same thing!! When I show them how to properly use the table they're like, "Oh... OOOOHHHH!"
April 13, 2011 at 1:36 pm
At the risk of exposing how much I might not know, why all the alter statements changing fields from tinyint to int, as well as NOT NULL? I would assume that the changing of populated columns to be NOT NULL helps performance, (and I'll be happy to read why if someone could point me to where to search for that hint) but why not define the int fields as int at table create?
Other than that, I have every plan to make use of this - kind of a Swiss Army Knife utlity table. Thanks in advance for the slightly off topic answer to my question.
Bob
April 14, 2011 at 6:06 am
That's probably just a bad on my side. I may have originally set the columns to INT in the CREATE TABLE statement then changed it to TINYINT and forgot to adjust the corresponding ALTER TABLE statement. The NOT NULL is added after the fact because until the column is populated with data during the build, it can be NULL, but after it shouldn't be. It allows for error checking (if any values get missed during population) and allows for a developer to know that the column should always contain data when looking at the table definition.
September 9, 2013 at 7:12 am
CELKO (7/21/2010)
4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday (UK Boxing Day is the only one).
2nd Jan should also be in Scotland
September 9, 2013 at 7:52 am
Date tables are amazing! I can't tell you how many times I've found this simple little thing to be a huge time saver. Just being able to bang your data against a pre-determined date set can be the difference in getting a report back in a few seconds to a few hours.
My calendar table also had holidays in it (I needed those for certain charges to apply). I remember the first time I built the table and told my colleague about it, he thought I was crazy and had just wasted my time.
The first time I showed him how to easily generate a report that spanned a given week however, he changed his tune.
Thanks for this article, it included a lot of things I never included but could see being used.
Ad maiorem Dei gloriam
September 9, 2013 at 8:05 am
Thanks for the kind words. 🙂 Funny thing is I too had a similar situation with one colleague. Changed his tune too once he saw the power behind it. 🙂
September 9, 2013 at 11:25 am
Thanks much. I imagine most people have written some version of this with the columns they most need, but a single script collecting them all is great. I've got my own with a handful of our holidays and some other stuff.
Because this script is a one-time run and doesn't need to be efficient, those wanting is_businessday can do what I do and just add it at the end, calculating it based on is_weekday and a to-be-added holiday column... which would probably require customization for most places. Celko posted awesome resources, my own I'm-too-lazy-this-is-good-enough code for some standard U.S. holidays was was:
[Holiday] AS
CASE
WHEN MONTH(date)=1 AND DAY(date)=1 THEN 'New Years Day'
WHEN MONTH(date)=5 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=2 AND DAY(date) BETWEEN 25 AND 31 THEN 'Memorial Day'
WHEN MONTH(date)=7 AND DAY(date)=4 THEN 'Independence Day'
WHEN MONTH(date)=9 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=2 AND DAY(date) BETWEEN 1 AND 7 THEN 'Labor Day'
WHEN MONTH(date)=11 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=5 AND DAY(date) BETWEEN 22 and 28 THEN 'Thanksgiving'
WHEN MONTH(date)=12 AND DAY(date)=25 THEN 'Christmas Eve'
WHEN MONTH(date)=12 AND DAY(date)=26 THEN 'Christmas Day'
ELSE '' -- or NULL depending on your preference
END /* CASE */,
September 9, 2013 at 11:44 am
Awesome! Thanks for sharing. 🙂
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply