Joining datetime dates to calendar table
-
I'm looking for some input and comments/ideas.
I've got a transaction table (orders) which contains an OrderDate column, which is a datetime column.
I want to join this table to a (daily) calendar table on date and clearly I need to somehow deal with the fact that OrderDate may have a time component (which I am not concerned with, for the purposes of this exercise).
I know that I could do something like
where Calendar.Date >= cast(OrderDate as date)and Calendar.Date < dateadd(day, 1, cast(OrderDate as date))But it seems a little cumbersome, and potentially quite expensive.
Another idea is that I add a computed column to the OrderDate table which is Cast(OrderDate as Date) and then index it. I could then join the calendar table directly to this. What do people think of this idea?
Thanks for any input.
-
...
Another idea is that I add a computed column to the OrderDate table which is Cast(OrderDate as Date) and then index it. I could then join the calendar table directly to this. What do people think of this idea?
...
I think that above will give you the best performance of your JOIN, do this if situation allows (as it will affect performance of inserting into this table...)
-
Can you change the Calendar table? If so, you could add a computed persisted column " NextDay as dateadd(day, 1, Calendar.Date) "
Your join would then be
where OrderDate >= Calendar.Date
and OrderDate < Calendar.NextDay
I'm afraid I don't know how this will perform though
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
If you can make a small mod to your Calendar table... I usually create a Calendar table with 2 date columns to easily solve such an eventuality. The first column is, of course, the date for the current row. The second column (which most people don't have) is the date of the next day.
--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 (3/30/2012)
If you can make a small mod to your Calendar table... I usually create a Calendar table with 2 date columns to easily solve such an eventuality. The first column is, of course, the date for the current row. The second column (which most people don't have) is the date of the next day.Jeff, that's a great idea - implementing it now, with a small tweak. Column 2 will be date1 + 23:59:59.997 so that I can use 'BETWEEN date1 and date2' in my WHERE clause.
Many thanks
Phil
-
Phil Parkin (3/30/2012)
Jeff Moden (3/30/2012)
If you can make a small mod to your Calendar table... I usually create a Calendar table with 2 date columns to easily solve such an eventuality. The first column is, of course, the date for the current row. The second column (which most people don't have) is the date of the next day.Jeff, that's a great idea - implementing it now, with a small tweak. Column 2 will be date1 + 23:59:59.997 so that I can use 'BETWEEN date1 and date2' in my WHERE clause.
Many thanks
Phil
Are you going to add another column to handle using BETWEEN when you move to SQL Server 2008 or later and find yourself using the DATETIME2 data type? I would go with Jeff's suggestion and use >= and < in the where clause.
-
Lynn Pettis (3/30/2012)
Phil Parkin (3/30/2012)
Jeff Moden (3/30/2012)
If you can make a small mod to your Calendar table... I usually create a Calendar table with 2 date columns to easily solve such an eventuality. The first column is, of course, the date for the current row. The second column (which most people don't have) is the date of the next day.Jeff, that's a great idea - implementing it now, with a small tweak. Column 2 will be date1 + 23:59:59.997 so that I can use 'BETWEEN date1 and date2' in my WHERE clause.
Many thanks
Phil
Are you going to add another column to handle using BETWEEN when you move to SQL Server 2008 or later and find yourself using the DATETIME2 data type? I would go with Jeff's suggestion and use >= and < in the where clause.
Err, no
Thanks for the insight Lynn. -
Phil Parkin (3/30/2012)
Jeff Moden (3/30/2012)
If you can make a small mod to your Calendar table... I usually create a Calendar table with 2 date columns to easily solve such an eventuality. The first column is, of course, the date for the current row. The second column (which most people don't have) is the date of the next day.Jeff, that's a great idea - implementing it now, with a small tweak. Column 2 will be date1 + 23:59:59.997 so that I can use 'BETWEEN date1 and date2' in my WHERE clause.
Many thanks
Phil
Just to emphasize what Lynn has already stated... using 23:59:59.997 is both an over complication and an opportunity to miss data with the new datatypes. The dates in your calendar table should be "whole" dates (midnight time) and you should us the ">= AND <" form in ON and WHERE clauses. It will make your code bullet proof in any situation that I can think of. I try to not use BETWEEN even if columns are supposedly guaranteed to always be whole dates and even when the new DATE datatype is being used because future requirements and modifications can cause the datatype of a column to be changed.
--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)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply