Useful Dates: The Many Uses of Date Tables

Question

Post reply

Useful Dates: The Many Uses of Date Tables

CheeseMan316

SSC Veteran

Points: 240
More actions
December 16, 2008 at 11:35 pm

#204053

Comments posted to this topic are about the item Useful Dates: The Many Uses of Date Tables

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

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 1

Anipaul

SSC-Insane

Points: 24681

December 17, 2008 at 3:16 am

#913669

Nice article. Useful one...

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 2

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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.

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 4

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

jrose-664261 Grasshopper Points: 16 More actions · Answer 5

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Same problem with dates!!!

You need to store all the data for the minimum safe range needed... which is often a buttload of data!

mike.byrnes Grasshopper Points: 19 More actions · Answer 7

If my calculations are correct, then if each year took 5Mb then 200 years would be 1 Gb worth of data, not 100Mb.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rkonopka-828284 Grasshopper Points: 19 More actions · Answer 9

Instead of double converting (first to varchar then back to datetime), wouldn't it be simpler and "cleaner" to do this:

select dateadd(dd,datediff(dd,0,getdate()),0)

Enjoyed the article

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Jeff, I am very interested in your findings! Perhaps this is one of those "step outside the box" situations where we all come out with something very useful and powerful.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 11

rkonopka (12/17/2008)
Instead of double converting (first to varchar then back to datetime), wouldn't it be simpler and "cleaner" to do this:
select dateadd(dd,datediff(dd,0,getdate()),0)
Enjoyed the article

Yes... and faster to boot.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 12

TheSQLGuru (12/17/2008)
Jeff, I am very interested in your findings! Perhaps this is one of those "step outside the box" situations where we all come out with something very useful and powerful.

I was thinking the very same thing, Kevin... my first impression was "What, are you kidding me? How'z that gonna work?" Then I remembered the saying I have framed in my cube... "Before you can think outside the box, you must first realize... you're in a box!" So, I'm gonna find out, "How'z that gonna work" because like Sergiy says, "A Developer must not guess... a Developer must KNOW!" Because of the denormalized state of the table, it could be very fast...

... it'll take a bit to do the kind of testing I'm thinking of...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CheeseMan316 SSC Veteran Points: 240 More actions · Answer 13

Joe Celko (12/17/2008)
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?

The reason that I added the date_id column is to allow the dimension to be referenced by a 4-byte value instead of an 8 byte value if the application calls for linking data to a coordinate.

CheeseMan316 SSC Veteran Points: 240 More actions · Answer 14

mike.byrnes (12/17/2008)
If my calculations are correct, then if each year took 5Mb then 200 years would be 1 Gb worth of data, not 100Mb.

I guess I was incorrect that it's 500KB per year then because here is the space used by my table.

name rows reserved data index_size unused

-------------------- ----------------- -------- ---------- ------------------

util_time_coordinate 1829625 99792 KB 52400 KB 47296 KB 96 KB