June 14, 2011 at 1:40 am
I have an application where everything is about project dates - meeting dates, timeline dates and microtimeline dates - every step in every process has a target date and virtually every query involves dates and dates calculations.
So I created a Dates table with the next 30 years of dates in it.
The columns are:
DateID int - PK
theDate - smalldatetime
DayID - tinyint - values 1 to 7 representing Sunday, Monday, Tuesday etc
MonthID - tinyint - values 1 to 12 representing Jan, Feb, Mar etc.
So, without using date functions, I can determine which day of the week a date falls on etc.
I need to be able to determine what is the 3rd Wednesday of the 'next' month. So I might have a DateID that is 792 - which in my Dates table represents 4th March 2012. How can I determine the DateID of the 3rd Wednesday in the following month?
If I do this ...
DECLARE @thisMonthID tinyint
SELECT @thisMonthID = MonthID FROM tblDates WHERE DateID = 792
... it gets me the MonthID of the date in question so, adding one to this gives me the MonthID of the next month.
DECLARE @nextMonthID tinyint
SET @nextMonthID = @thisMonthID + 1
So, I've got the ID for the next month ... how can I find the third DayID in this month which has a value of 4 (which is Wednesday) - bearing in mind the MonthIDs are not unique - they count from 1 to 12 and start again.
Thanks for any help.
June 14, 2011 at 4:02 pm
sku370870 (6/14/2011)
I have an application where everything is about project dates - meeting dates, timeline dates and microtimeline dates - every step in every process has a target date and virtually every query involves dates and dates calculations.So I created a Dates table with the next 30 years of dates in it.
The columns are:
DateID int - PK
theDate - smalldatetime
DayID - tinyint - values 1 to 7 representing Sunday, Monday, Tuesday etc
MonthID - tinyint - values 1 to 12 representing Jan, Feb, Mar etc.
So, without using date functions, I can determine which day of the week a date falls on etc.
I need to be able to determine what is the 3rd Wednesday of the 'next' month. So I might have a DateID that is 792 - which in my Dates table represents 4th March 2012. How can I determine the DateID of the 3rd Wednesday in the following month?
If I do this ...
DECLARE @thisMonthID tinyint
SELECT @thisMonthID = MonthID FROM tblDates WHERE DateID = 792
... it gets me the MonthID of the date in question so, adding one to this gives me the MonthID of the next month.
DECLARE @nextMonthID tinyint
SET @nextMonthID = @thisMonthID + 1
So, I've got the ID for the next month ... how can I find the third DayID in this month which has a value of 4 (which is Wednesday) - bearing in mind the MonthIDs are not unique - they count from 1 to 12 and start again.
Thanks for any help.
Are you open to adding a couple more columns to your date table?
DateID int - PK
TheDate - smalldatetime
DayID - tinyint - values 1 to 7 representing Sunday, Monday, Tuesday etc
MonthID - tinyint - values 1 to 12 representing Jan, Feb, Mar etc.
YearID - smallint - values 1 to 2500 representing the year
DayInstanceInMonth - tinyint - values 1-5 representing the n-th time this day has occurred in the month
The you can do things like this:
WITH cte(TheDate)
AS (
-- get the row for a month from your date (let DATEADD worry about December-January)
SELECT DATEADD(MONTH, 1, TheDate)
FROM tblDates
WHERE DateID = 792
),
cte2(YearID, MonthID)
AS (
-- get the year and month for your new date
SELECT t.YearID,
t.MonthID
FROM tblDates t
JOIN cte ON t.TheDate = cte.TheDate
)
-- get the third Wednesday for your new month and year
SELECT DateID
FROM tblDates t
JOIN cte2 ON t.YearID = cte.YearID
AND t.MonthID = cte.MonthID
WHERE -- Wednesday
t.DayID = 4
-- third instance
AND t.DayInstanceInMonth = 3 ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 10:37 pm
opc.three (6/14/2011)
DayInstanceInMonth - tinyint - values 1-5 representing the n-th time this day has occurred in the month
Most people don't think of such a thing. I was going to write an article on that and another "special" column but have put it off for way too long. Maybe you'd like to break the ice on that instead?:-)
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2011 at 10:41 pm
sku370870 (6/14/2011)
I have an application where everything is about project dates - meeting dates, timeline dates and microtimeline dates - every step in every process has a target date and virtually every query involves dates and dates calculations.So I created a Dates table with the next 30 years of dates in it.
The columns are:
DateID int - PK
theDate - smalldatetime
DayID - tinyint - values 1 to 7 representing Sunday, Monday, Tuesday etc
MonthID - tinyint - values 1 to 12 representing Jan, Feb, Mar etc.
So, without using date functions, I can determine which day of the week a date falls on etc.
I need to be able to determine what is the 3rd Wednesday of the 'next' month. So I might have a DateID that is 792 - which in my Dates table represents 4th March 2012. How can I determine the DateID of the 3rd Wednesday in the following month?
If I do this ...
DECLARE @thisMonthID tinyint
SELECT @thisMonthID = MonthID FROM tblDates WHERE DateID = 792
... it gets me the MonthID of the date in question so, adding one to this gives me the MonthID of the next month.
DECLARE @nextMonthID tinyint
SET @nextMonthID = @thisMonthID + 1
So, I've got the ID for the next month ... how can I find the third DayID in this month which has a value of 4 (which is Wednesday) - bearing in mind the MonthIDs are not unique - they count from 1 to 12 and start again.
Thanks for any help.
You do know about "DATENAME" don't you?
SELECT DATENAME(mm,GETDATE()), DATENAME(dw,GETDATE())
(No column name)(No column name)
JuneWednesday
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2011 at 10:44 pm
Also, a Calendar table works much faster if the PK and the Clustered Index are on the Date column. Unless it's there for purposes of some type of table automation, there's no need for an ID/IDENTITY column on a Calendar table.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2011 at 2:00 am
To opc.three - I most certainly am open to adding a couple of extra columns!
Thanks very much for your help.
And to Jeff - thanks also for your help.
Regarding your comments about the PK ... I have always just made sure that every table (well, almost every table) has an identity column used as the PK.
But you're saying the Dates table will run better with the Date column used as the Primary Key and 'a clustered index' ... how do 'set' a clustered index?
June 15, 2011 at 7:25 am
sku370870 (6/15/2011)
Regarding your comments about the PK ... I have always just made sure that every table (well, almost every table) has an identity column used as the PK.But you're saying the Dates table will run better with the Date column used as the Primary Key and 'a clustered index' ... how do 'set' a clustered index?
When you make the PK, define it as "Non Clustered". Then create an index on the date column like this...
CREATE CLUSTERED INDEX IX_Calendar_Date ON dbo.Calendar (DATE) WITH FILLFACTOR = 100
Since the table is static in nature, you don't want any "open" rows that the system has to read past. That's what the fill factor of 100 does for you. Most systems use the default of "0" which is almost the same as a fill factor of 100 but I never take the chance... I always declare the fill factor when making most of my tables depending on the nature of the table (Static = 100, Mildly transaction = 90, etc.)
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2011 at 7:56 am
I agree that a clustered index on date makes the most sense for a date table, at least for the majority of applications.
I think a sequential number for the dates is very useful in many situations. In the function on the following link, I define the DATE_ID = Days since 1753-01-01. The function also has two other columns that contain unique numbers for each date: JULIAN_DATE and MODIFIED_JULIAN_DATE
The function also returns sequential numbers for years, quarters, months, and weeks starting on any day of the week. Makes queries for next or last year, quarter, month, or week fairly simple, and a query for something like the 8th week after the start of last quarter is also fairly simple.
Date Table Function F_TABLE_DATE
June 15, 2011 at 1:31 pm
Jeff Moden (6/15/2011)
sku370870 (6/15/2011)
Regarding your comments about the PK ... I have always just made sure that every table (well, almost every table) has an identity column used as the PK.But you're saying the Dates table will run better with the Date column used as the Primary Key and 'a clustered index' ... how do 'set' a clustered index?
When you make the PK, define it as "Non Clustered". Then create an index on the date column like this...
CREATE CLUSTERED INDEX IX_Calendar_Date ON dbo.Calendar (DATE) WITH FILLFACTOR = 100Since the table is static in nature, you don't want any "open" rows that the system has to read past. That's what the fill factor of 100 does for you. Most systems use the default of "0" which is almost the same as a fill factor of 100 but I never take the chance... I always declare the fill factor when making most of my tables depending on the nature of the table (Static = 100, Mildly transaction = 90, etc.)
Thanks again for your reply.
Just so I'm quite clear ... I remove the Primary Key from the DateID field and create a Non Clustered Primary Key on the field that contains the actual dates ... and then create the Clustered Index as your code?
I've done that and now in the Indexes/Keys window I have ...
IX_tblDates_theDate ... which shows Columns - theDate(ASC) and Create as Clustered - Yes and Fill Specification - 100
PK_tblDates ... which shows Columns - theDate(ASC) and Create as Clustered - No
Is this right?
June 15, 2011 at 6:14 pm
No... following your standard of having an ID column on most tables, leave the PK on the ID column (makes some forms of automation a lot easier). Just drop it and recreate it as a Non-Clustered PK. Then create a clustered index on the actual date column.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2011 at 6:22 pm
Ah... almost forgot. You might want to take a look at Michael Valentine Jones' post above... the concept of using the underlying "date serial number" (number of days since 1900-01-01) is one of those good "tricks" that a whole lot of people just don't think of. Same goes for using increments on the other date parts that Michael suggests.
opc.three has some good suggestions for some additional columns, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2011 at 10:10 am
Okay, thanks again.
June 16, 2011 at 11:46 am
Jeff Moden (6/14/2011)
Also, a Calendar table works much faster if the PK and the Clustered Index are on the Date column. Unless it's there for purposes of some type of table automation, there's no need for an ID/IDENTITY column on a Calendar table.
Jeff,
I've found that the cluster on date is faster with daily type calendar tables. However, for monthly and weekly, which for me has been the more usual business case, the cluster on the IDENTITY with a unique non-clustered on the Start/End dates for the period works better for some reason. The main purpose for the monthly/weekly/quarterly calendar tables is for grouping in reports and for ranges of months/weeks/quarters. Having the sequential ID makes it easier to do ranges since you only have to use integer math on the ID to get the range - e.g. between MonthID and MonthID - 6 to get a 6 month range in the report.
I also thought that opc.three's addition of an instance column was brilliant. I've never had a business case for that, but I'm putting it my tool chest.
Todd Fifield
June 16, 2011 at 2:01 pm
Jeff Moden (6/14/2011)
opc.three (6/14/2011)
DayInstanceInMonth - tinyint - values 1-5 representing the n-th time this day has occurred in the monthMost people don't think of such a thing. I was going to write an article on that and another "special" column but have put it off for way too long. Maybe you'd like to break the ice on that instead?:-)
tfifield (6/16/2011)
...I also thought that opc.three's addition of an instance column was brilliant.
...
Thanks gents :blush: Writing? Love to try my hand at it but do not know where to start. I sent you an email.
Michael Valentine Jones (6/15/2011)
...Date Table Function F_TABLE_DATE
Thanks for sharing! *adds function to toolkit*
Jeff Moden (6/15/2011)
Ah... almost forgot. You might want to take a look at Michael Valentine Jones' post above... the concept of using the underlying "date serial number" (number of days since 1900-01-01) is one of those good "tricks" that a whole lot of people just don't think of....
This is a very cool thread 😎
I have used the trick to model dates as INTs in a columnar name/attribute/value store. It goes something like this for those that have not seen it:
DECLARE @dt DATETIME = '1/1/1900' ;
SELECT CAST(@dt AS INT) ;
SELECT CAST(GETDATE() AS INT) ;
SELECT DATEDIFF(DAY, CAST(@dt AS INT), CAST(GETDATE() AS INT)) ;
That said, Michael's script has "DATE_ID = Days since 1753-01-01". I am not modeling anything more than 111 years old...was it done this way in the function just for the additional range of dates?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 3:22 pm
opc.three (6/16/2011)
...
That said, Michael's script has "DATE_ID = Days since 1753-01-01". I am not modeling anything more than 111 years old...was it done this way in the function just for the additional range of dates?
I did it that way because 1753-01-01 is the earliest possible DATETIME value, and the programming is really no different or more trouble.
That function was meant to support the widest possible range of applications, so it supports a valid date range of 1754-01-01 through 9997-12-31. I left off a year at the beginning and two years at the end of the possible DATETIME values to make it simpler to program the function, and figured most people could make do with the 8245 year range it does support.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply