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


Useful Dates: The Many Uses of Date Tables


Useful Dates: The Many Uses of Date Tables

Author
Message
rkonopka-828284
rkonopka-828284
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18
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
TheSQLGuru
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52036 Visits: 8795
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
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340529 Visits: 42644
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340529 Visits: 42644
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
CheeseMan316
CheeseMan316
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 71
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
CheeseMan316
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 71
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
CheeseMan316
CheeseMan316
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 71
Jeff Moden (12/17/2008)
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.

The "Text Editor" I used was SQL Server Management Studio for all SQL Code. The tested and working code was then copied into the article which is a word document typed in OpenOffice. I then emailed the entire DOC file to Steve so it is something in the publishing that is causing it.

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.

Good idea. When I submitted this one, the first hadn't been published yet for me to include the link. I will have to go back and do it now.


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.

Great idea and I will do that and fix them both up because this happened with the first one too.
CheeseMan316
CheeseMan316
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 71
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


I've started to use that approach for time stripping but this article was written before I adopted that method. Thanks for the input!
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52036 Visits: 8795
CheeseMan316 (12/17/2008)
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



It is customary and useful to have your int date keys be formatted CCYYMMDD, at least in the classical date dimension scenario. Identities are really not helpful here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
The Dixie Flatline
The Dixie Flatline
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19776 Visits: 6908
I've worked with a dates table to find "missed" days, and also used CTES to generate sets of dates on the fly using a tally table to increment some number of days from a given starting date. Obviously having a single date column and a single clustered index require less disk space, and the cte approach required next to nothing in disk, although you pay for it in CPU cycles. But your table extends the concept to hours, months, and quarters. Could you give us a real world example or two using other dimensions besides day?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
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