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 «««23456»»

Date Calendar Expand / Collapse
Author
Message
Posted Thursday, April 14, 2011 6:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 341, Visits: 685
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.
Post #1093442
Posted Monday, September 9, 2013 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 207, Visits: 823
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
Post #1492763
Posted Monday, September 9, 2013 7:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:59 PM
Points: 155, Visits: 66
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
Post #1492792
Posted Monday, September 9, 2013 8:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 341, Visits: 685
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. :)
Post #1492797
Posted Monday, September 9, 2013 11:25 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:48 AM
Points: 12, Visits: 65
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 */,

Post #1492881
Posted Monday, September 9, 2013 11:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 341, Visits: 685
Awesome! Thanks for sharing. :)
Post #1492887
Posted Tuesday, September 10, 2013 5:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Great script!

 
Post #1493397
Posted Tuesday, September 10, 2013 5:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 341, Visits: 685
Thank you!
Post #1493399
Posted Tuesday, September 10, 2013 9:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Sean

I haven't had a chance to review the entire script in detail nor have I gone back to read all the comments so if the following issue has already been discussed, please point it out.

Did you include an option to display the cardinality of the days, i.e., '2nd of March', '3rd of March', '4th of March', etc.

Thanks

 
Post #1493428
Posted Wednesday, September 11, 2013 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 341, Visits: 685
No, not yet. Very few changes in a long time actually. SSC just decided to re-feature it despite nothing really new in the script.
Post #1493752
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse