Calendar Tables

  • tfifield

    SSCrazy Eights

    Points: 9655

    Comments posted to this topic are about the item Calendar Tables

  • René Berends

    Valued Member

    Points: 52

    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

    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

    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

    SSCertifiable

    Points: 7210

    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

    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

    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

    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

    SSC Eights!

    Points: 983

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

  • awilbournsqlcentral

    Old Hand

    Points: 363

    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

    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: 890

    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

    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

    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

    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!

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

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