Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Todd Fifield » Calendar Tables
Add to briefcase
40 posts, Page 1 of 4
1234»»»

Calendar Tables Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
tfifield
Posted Wednesday, July 14, 2010 10:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 05, 2013 8:10 PM
Points: 958, Visits: 2,873
Comments posted to this topic are about the item Calendar Tables
Post #952833
René Berends
Posted Thursday, July 15, 2010 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:41 AM
Points: 2, Visits: 151
Good article, I couldn't agree more! Some things regarding dates and calendars cannot (easily) be caught in formulae, take for example:

1. Weeknumbering around newyear. Some don't want a week 53 and sure don't want January 1st to be part of week 53 of the last year
2. Working days: is a specific date a working day or a non-working day?
3. Rare, but I now some cases: for example invoice dates should always fall on a working day. What if we invoice on a Sunday? Move it to the next working day
4. Quarter / Period based on a 4-4-5 week schedule

Things like these I always solve using a calendar table. Seems more logic to the customer as well!

René

Kind regards,
René Berends
Post #952884
Tom Williams-175034
Posted Thursday, July 15, 2010 2:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:01 AM
Points: 7, Visits: 37
Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.

I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.

Just my £0.02.
Post #952915
Jason-299789
Posted Thursday, July 15, 2010 2:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 836, Visits: 2,192
Rather than using the Tally method have you considered using a CTE, the code below would work just as well and be a little more flexible. 

DECLARE @DateFrom as DateTime
Declare @DateTo As DateTime

Select @DateFrom = '01-Jan-2000', @DateTo='31-Dec-2010'

;WITH GetDateId(Id,MonthDate) AS
	(
	 SELECT CAST(@DateFrom as Int) AS Id,@DateFrom DaysDate
	 UNION ALL
	 SELECT Id + 1,Cast(Id+1 as DATETIME) MonthDate
	 FROM GetDateId gr
	 Where Cast(Id as SmallDateTime) < @DateTo 
)
SELECT
	MonthDate, 
	Upper(Left(DateName(M,MonthDate),3))+'-'+Right(Year(MonthDate),2)
FROM
	  GetDateId
WHERE MonthDate>=@DateFrom 
OPTION (MAXRECURSION 0);

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #952917
ziangij
Posted Thursday, July 15, 2010 4:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,551, Visits: 359
i have populated data till year 2050 as a contigency measure

Tom Williams-175034 (7/15/2010)
Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.

I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.

Just my £0.02.
Post #952970
Tom Williams-175034
Posted Thursday, July 15, 2010 4:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:01 AM
Points: 7, Visits: 37
ziangij (7/15/2010)
i have populated data till year 2050 as a contigency measure

Tom Williams-175034 (7/15/2010)
Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.

I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.

Just my £0.02.


This is how the Y2K problem came about; programmers who figured their application wouldn't be about in 40 years time... Have some faith in your applications - People might still be using it then!
Post #952972
David Data
Posted Thursday, July 15, 2010 4:50 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:21 AM
Points: 115, Visits: 720
I use a date table allowing lookup of Month, Season etc. by date and customer, provided by Marketing! This may sound crazy but it's a good way to deal with the fact that different customers have different 'Sales' periods, start their Summer Season on different dates, etc.

Of course we make sure the calendar runs ahead of the current year, AND automatically email out a clear error report if a date appears that's not in the table.
Post #952987
Michael Nicol
Posted Thursday, July 15, 2010 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:17 AM
Points: 3, Visits: 46
Hey Todd,

Nice article. As I'm primarily a developer and have recently dipped my feet in the DBA world, I'd like to ask some questions around some points that I am unsure of.

1. Wouldn't the join between CalMonth and Sales.SalesOrderHeader be more efficient if you added a Foreign key relationship from Sales.SalesOrderHeader to the MonthID in CalMonth? There would have to be some initial update to history data to populate the data (in Sales.SalesOrderHeader), and the data could then be maintained, either through changes to the application, or by adding a (don't kill me yet, I know.. I hate them too, but they have their place) FOR INSERT and perhaps FOR UPDATE trigger (ONLY if an application code change is a barrier)?

2. You mentioned creating a covering index on Stay_ArrivalRevenue to avoid excessive key lookups. Would it not be better to create the index and only INCLUDE the fields to satisfy the SELECT portion?

i.e. Instead of 
CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate, TotalRevenue)

rather use

CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate) INCLUDE (TotalRevenue)

This would preserve a better key density at higher levels of the index while still avoiding excessive key lookups.

As mentioned previously, I'm primarily a Dev and, at the moment, am still learning more and more about the DBA world. My points above are purely to help me understand better. Any insights would be appreciated.

Regards
Mike Nicol
Post #953008
Karen Lopez - InfoAdvisors
Posted Thursday, July 15, 2010 5:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 8:16 AM
Points: 29, Visits: 119
With an Identity, isn't it a risk to assume the value is sequential and ordered?
Post #953037
awilbournsqlcentral
Posted Thursday, July 15, 2010 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 7:56 AM
Points: 18, Visits: 41
One thing I think could be improved is the key for the tables should not be a random key, I would suggest to use the date as the key. So in your month table you make the key be YEARMONTH, so 200001.

Further example:
201001
201002
201003
201004
201005
201006
201007
201008
201009
201010
201011
201012
Post #953039
« Prev Topic | Next Topic »

Add to briefcase
40 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.