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 123»»»

Useful Dates: The Many Uses of Date Tables Expand / Collapse
Author
Message
Posted Tuesday, December 16, 2008 11:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 3, 2011 8:48 AM
Points: 26, Visits: 71
Comments posted to this topic are about the item Useful Dates: The Many Uses of Date Tables
Post #620965
Posted Wednesday, December 17, 2008 3:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,439, Visits: 1,400
Nice article. Useful one...


Post #621035
Posted Wednesday, December 17, 2008 6:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Have you considered combining your records? Instead of 1 record for every hour in a day, and one record for the every day, and one record for every month, etc. each record would contain the hour, the day, the month, the quarter, the year. You do save a little space on records and indexes, but the main benefit is that you have all the information about the "period" you are looking at. You do need more logic in the where to make sure you don't over select records.

<><
Livin' down on the cube farm. Left, left, then a right.
Post #621173
Posted Wednesday, December 17, 2008 7:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121
The calendar date is the natural key, so the magically vague "id" is not needed. The table is not normalized to 1NF -- you have data and metadata in it. What do the NULLs mean?

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #621188
Posted Wednesday, December 17, 2008 7:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
Hey Joe, don't you have an article or two posted on the subject.

It might be a good place to put a link to it!

TIA.
Post #621193
Posted Wednesday, December 17, 2008 7:14 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 8:24 AM
Points: 160, Visits: 979
Just one suggestion, make the key an int, and populate with the date yyyymmdd (20081216), instead of an identity column.

You might want to create a separate table, for fiscal calendar, that could also be joined to your date table. Again, using the date as an int key. This would contain data that indicates which fiscal week, quarter, year, etc.

Leonard
Post #621198
Posted Wednesday, December 17, 2008 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 21, 2011 12:20 PM
Points: 2, Visits: 55
I actually prefer using the numbers method, as in your util_nums article. it allows more flexibility and doesn't require storage of set values. The only disadvantage to the numbers method is that you have to store enough numbers for a maximum range.
Post #621219
Posted Wednesday, December 17, 2008 7:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
Same problem with dates!!!

You need to store all the data for the minimum safe range needed... which is often a buttload of data!
Post #621229
Posted Wednesday, December 17, 2008 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 8:37 AM
Points: 1, Visits: 11
If my calculations are correct, then if each year took 5Mb then 200 years would be 1 Gb worth of data, not 100Mb.
Post #621240
Posted Wednesday, December 17, 2008 8:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 35,547, Visits: 32,138
Brandon,

Not sure what the text editor you used may have done to you, but there's a lot of missing spaces in your code... for example CREATE TABLE has become CREATETABLE and SET @variable has become SET@variable. There are many other accidental concatenations throughout the code. It might be why there's not much discussion or rating on this article, so far... people just don't wanna take the time to fix posted code.

Also, you may want to add a link to your article on how to build your numbers table... it would help people that just don't know about such things, a lot. Either that, or it's short enough to just use in the code.

Contact Steve Jones for how to edit your article if you're interested in doing such a thing. It's not difficult and Steve is very happy to help on these things.

I've not played with the idea of an NVP (Name Value Pair) table like this for a calendar table. It's an interesting and different idea... it certainly makes indexing it a breeze and I'm gonna play with it and see how it does against more traditonal calendar tables. One of the things I'm going to play with, is the idea of using the clustered index to "partition" the dates in groups by the dimension column whilst including the date as the second column and, maybe, trying to do a partioned view on it. Like I said, it's an interesting and different idea.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #621243
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse