SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calendar Tables


Calendar Tables

Author
Message
tfifield
tfifield
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3781 Visits: 2890
Comments posted to this topic are about the item Calendar Tables
René Berends
René Berends
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 244
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
Tom Williams-175034
Tom Williams-175034
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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.
Jason-299789
Jason-299789
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 3232
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
ziangij
ziangij
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3955 Visits: 380
i have populated data till year 2050 as a contigency measure :-D

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.

Tom Williams-175034
Tom Williams-175034
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 37
ziangij (7/15/2010)
i have populated data till year 2050 as a contigency measure :-D

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!
David Data
David Data
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1301 Visits: 828
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.
Michael Nicol
Michael Nicol
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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
Karen Lopez @datachick
Karen Lopez @datachick
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 193
With an Identity, isn't it a risk to assume the value is sequential and ordered?
awilbournsqlcentral
awilbournsqlcentral
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 47
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search