Calendar Tables

Question

Calendar Tables

tfifield

SSCrazy Eights

Points: 9655
More actions
July 14, 2010 at 10:05 pm

#223394

Comments posted to this topic are about the item Calendar Tables

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply

René Berends Valued Member Points: 52 More actions · Answer 1

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 SSC Enthusiast Points: 103 More actions · Answer 2

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 SSC-Insane Points: 21601 More actions · Answer 3

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 SSCrazy Eights Points: 8066 More actions · Answer 4

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.

Tom Williams-175034 SSC Enthusiast Points: 103 More actions · Answer 5

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!

David Data SSCrazy Points: 2965 More actions · Answer 6

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 SSC Enthusiast Points: 117 More actions · Answer 7

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

......................... SSC Eights! Points: 983 More actions · Answer 8

With an Identity, isn't it a risk to assume the value is sequential and ordered?

awilbournsqlcentral Old Hand Points: 363 More actions · Answer 9

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

Victor Kirkpatrick Hall of Fame Points: 3672 More actions · Answer 10

The key isn't random just because it's an identity, as long as when the records for the date table were created in the correct "month" order. And no, it isn't dangerous.

Nice article. I already have a numbers table that I use regularly, maybe it's time for a dates table like this.

As for for Tom's comments concerning staying away from tables like this where you are worried about not have data for a given year. Relax. You could load this table through 2500 and it would still easily reside in memory.

wagner crivelini SSC Eights! Points: 922 More actions · Answer 11

Nice article!

People tend to avoid time tables relying on SQL functions. This is a good approach if you don't need to do those calculations frequently.

I remember a project where people wanted to kill me when I designed a table with time period and correspondent details as worked hours in that period and so on.

When somebody decided to change those periods from calendar months to something different, programmers got crazy and came to me to see what to do....

Just to hear all we had to do was to run a couple of UPDATE statements in the previously infamous time table 😀

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

I was first introduced to calendar and time tables when I started learning about data warehousing. I have always thought they would be useful in the relational world as well. It is nice to see someone using them.

awilbournsqlcentral Old Hand Points: 363 More actions · Answer 13

Victor Kirkpatrick (7/15/2010)
The key isn't random just because it's an identity, as long as when the records for the date table were created in the correct "month" order. And no, it isn't dangerous.
Nice article. I already have a numbers table that I use regularly, maybe it's time for a dates table like this.
As for for Tom's comments concerning staying away from tables like this where you are worried about not have data for a given year. Relax. You could load this table through 2500 and it would still easily reside in memory.

As a BI developer that is dealing with a Date Dimension that used such a means to create the key, it is a royal pain when you are trying to sift through millions of rows of data and where you are looking for a certain date range. Rather than having to go look up what value 5234 is, it would be nice to have the value be the date. This also helps developers if they want certain data and know the date, but only need to query on the date itself, so no need to join the extra table to do so, only need it if you need more dimension data. So a date table would be like 20100715 for today, something very easy for application developer to format and pass along.

Anyhow, I have been on the dev and DBA side and find that if you can help make your normalized data make a little since (with dates at least) it helps make finding issues in the data easier, because is it the app or the data is always the question.

GRScow SSC Rookie Points: 30 More actions · Answer 14

Good article on a key topic for db/warehouse developers like me.

It seems that from a maintenance viewpoint, it would be much easier to define all of the variations on the main date as computed fields. Adding date formats and derived dates is as simple as adding a calculated field to the table.

We have several significant data collection and reporting systems and various data sources which use various date formats (including mainframes where records are defined with YY fields and MM fields - text and int types, combined and split). Any date fields in a data table can be joined to the date table on the appropriate date field(s) and of course we always try to store the resulting records in a Sql native datetime field.

Seems like this provides the performance benefits of joining to a table along with the super-simple maintenance - there is only one step on one field when adding months to the table.

Cheers!