I decided to give this a real world try and implemented it in a financial database software to look at trends. I compared it to my stored procedures that I usually run and found that in general that this table approach was actually significantly faster. So instead of figuring out why my SP are slower, I think I might just stick with this "quick" and dirty solution. Great Job!
Anybody else try this out on a real set of tables? Any comparison against your SP?
-Eric
Discussion on:
View:
Show:
create a table of numbers (mine is called zNumbers, containing one field called Number type int), load it with a loop with the range 0 - 80,000. You can then load the above table with a single insert statement that runs in 3 seconds on my system:
INSERT INTO DateLookup (
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MonthName, YearDay,
DateDefinition,
CharacterDate,
WeekDay,
MonthNumber)
SELECT
CONVERT(VARCHAR(8), cast(Number as datetime), 112),
cast(Number as datetime),
YEAR(cast(Number as datetime)),
DATEPART(qq, cast(Number as datetime)),
DATEPART(ww, cast(Number as datetime)),
DATENAME(dw, cast(Number as datetime)),
DATEPART(dd, cast(Number as datetime)),
DATENAME(mm, cast(Number as datetime)),
DATEPART(dy, cast(Number as datetime)),
DATENAME(mm, cast(Number as datetime))
+ ' ' + rtrim(CAST(DATEPART(dd, cast(Number as datetime)) AS CHAR(2)))
+ ', ' + CAST(DATEPART(yy, cast(Number as datetime)) AS CHAR(4)),
CONVERT(VARCHAR(10), cast(Number as datetime), 101),
DATEPART(dw, cast(Number as datetime)),
DATEPART(mm, cast(Number as datetime))
from zNumbers
(I just hope this formats and displays reasonably!)
I used a similar technique when I needed a function for calculating the days between two dates but not counting weekends and holidays. A numbers table built that out beautifully.
INSERT INTO DateLookup (
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MonthName, YearDay,
DateDefinition,
CharacterDate,
WeekDay,
MonthNumber)
SELECT
CONVERT(VARCHAR(8), cast(Number as datetime), 112),
cast(Number as datetime),
YEAR(cast(Number as datetime)),
DATEPART(qq, cast(Number as datetime)),
DATEPART(ww, cast(Number as datetime)),
DATENAME(dw, cast(Number as datetime)),
DATEPART(dd, cast(Number as datetime)),
DATENAME(mm, cast(Number as datetime)),
DATEPART(dy, cast(Number as datetime)),
DATENAME(mm, cast(Number as datetime))
+ ' ' + rtrim(CAST(DATEPART(dd, cast(Number as datetime)) AS CHAR(2)))
+ ', ' + CAST(DATEPART(yy, cast(Number as datetime)) AS CHAR(4)),
CONVERT(VARCHAR(10), cast(Number as datetime), 101),
DATEPART(dw, cast(Number as datetime)),
DATEPART(mm, cast(Number as datetime))
from zNumbers
(I just hope this formats and displays reasonably!)
I used a similar technique when I needed a function for calculating the days between two dates but not counting weekends and holidays. A numbers table built that out beautifully.
Thanks for the code... For us Access Developers, here is some ADO/VBA...
Function CreateDateTable()
On Error GoTo CreateDateTable_Err
Dim dt As Date
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim tblNew As ADOX.Table
Dim cat As New ADOX.Catalog
Set con = Application.CurrentProject.Connection
Set cmd.ActiveConnection = con
Set cat.ActiveConnection = con
Set tblNew = New ADOX.Table
tblNew.Name = "tblDates"
tblNew.Columns.Append "DateKey", adInteger
tblNew.Columns.Append "DateFull", adDate
tblNew.Columns.Append "CharacterDate", adVarWChar, 10
tblNew.Columns.Append "FullYear", adVarWChar, 4
tblNew.Columns.Append "QuarterNumber", adUnsignedTinyInt
tblNew.Columns.Append "WeekNumber", adUnsignedTinyInt
tblNew.Columns.Append "WeekDayName", adVarWChar, 10
tblNew.Columns.Append "MonthDay", adUnsignedTinyInt
tblNew.Columns.Append "MonthName", adVarWChar, 12
tblNew.Columns.Append "YearDay", adInteger
tblNew.Columns.Append "DateDefinition", adVarWChar, 30
tblNew.Columns.Append "WeekDay", adUnsignedTinyInt
tblNew.Columns.Append "MonthNumber", adUnsignedTinyInt
tblNew.Keys.Append "PrimaryKey", adKeyPrimary, "DateKey"
cat.Tables.Append tblNew
Set tblNew = Nothing
Set rst = New ADODB.Recordset
rst.Open "tblDates", con, adOpenStatic, adLockOptimistic, adCmdTable
dt = #1/1/1900#
Do While dt #1/1/2100#
rst.AddNew
rst("DateKey") = Format(dt, "yyyymmdd")
rst("DateFull") = dt
rst("FullYear") = Year(dt)
rst("QuarterNumber") = DatePart("q", dt)
rst("WeekNumber") = DatePart("ww", dt)
rst("WeekDayName") = WeekdayName(DatePart("w", dt), False, vbSunday)
rst("MonthDay") = DatePart("d", dt)
rst("MonthName") = MonthName(DatePart("m", dt), False)
rst("YearDay") = DatePart("y", dt)
rst("DateDefinition") = MonthName(DatePart("m", dt)) & " " & CStr(DatePart("d", dt)) & ", " & CStr(DatePart("yyyy", dt))
rst("CharacterDate") = CStr(dt)
rst("WeekDay") = DatePart("w", dt)
rst("MonthNumber") = DatePart("m", dt)
rst.Update
dt = dt + 1
Loop
MsgBox "Creation complete."
CreateDateTable_Exit:
On Error Resume Next
Set tblNew = Nothing
rst.Close
Set rst = Nothing
Set con = Nothing
Exit Function
CreateDateTable_Err:
MsgBox "Error in CreateDateTable()."
Resume CreateDateTable_Exit
End Function
Function CreateDateTable()
On Error GoTo CreateDateTable_Err
Dim dt As Date
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim tblNew As ADOX.Table
Dim cat As New ADOX.Catalog
Set con = Application.CurrentProject.Connection
Set cmd.ActiveConnection = con
Set cat.ActiveConnection = con
Set tblNew = New ADOX.Table
tblNew.Name = "tblDates"
tblNew.Columns.Append "DateKey", adInteger
tblNew.Columns.Append "DateFull", adDate
tblNew.Columns.Append "CharacterDate", adVarWChar, 10
tblNew.Columns.Append "FullYear", adVarWChar, 4
tblNew.Columns.Append "QuarterNumber", adUnsignedTinyInt
tblNew.Columns.Append "WeekNumber", adUnsignedTinyInt
tblNew.Columns.Append "WeekDayName", adVarWChar, 10
tblNew.Columns.Append "MonthDay", adUnsignedTinyInt
tblNew.Columns.Append "MonthName", adVarWChar, 12
tblNew.Columns.Append "YearDay", adInteger
tblNew.Columns.Append "DateDefinition", adVarWChar, 30
tblNew.Columns.Append "WeekDay", adUnsignedTinyInt
tblNew.Columns.Append "MonthNumber", adUnsignedTinyInt
tblNew.Keys.Append "PrimaryKey", adKeyPrimary, "DateKey"
cat.Tables.Append tblNew
Set tblNew = Nothing
Set rst = New ADODB.Recordset
rst.Open "tblDates", con, adOpenStatic, adLockOptimistic, adCmdTable
dt = #1/1/1900#
Do While dt #1/1/2100#
rst.AddNew
rst("DateKey") = Format(dt, "yyyymmdd")
rst("DateFull") = dt
rst("FullYear") = Year(dt)
rst("QuarterNumber") = DatePart("q", dt)
rst("WeekNumber") = DatePart("ww", dt)
rst("WeekDayName") = WeekdayName(DatePart("w", dt), False, vbSunday)
rst("MonthDay") = DatePart("d", dt)
rst("MonthName") = MonthName(DatePart("m", dt), False)
rst("YearDay") = DatePart("y", dt)
rst("DateDefinition") = MonthName(DatePart("m", dt)) & " " & CStr(DatePart("d", dt)) & ", " & CStr(DatePart("yyyy", dt))
rst("CharacterDate") = CStr(dt)
rst("WeekDay") = DatePart("w", dt)
rst("MonthNumber") = DatePart("m", dt)
rst.Update
dt = dt + 1
Loop
MsgBox "Creation complete."
CreateDateTable_Exit:
On Error Resume Next
Set tblNew = Nothing
rst.Close
Set rst = Nothing
Set con = Nothing
Exit Function
CreateDateTable_Err:
MsgBox "Error in CreateDateTable()."
Resume CreateDateTable_Exit
End Function
I like self maintaining code. What do you think about:
put the sproc in a job, run once per year.
delete from DateLookup
SET @Date = '1/1/' + convert(char(4), datepart("yyyy",getdate)-100)
and
WHILE @Date '1/1/' + convert(char(4),datepart(yy,getdate()) + 100)
put the sproc in a job, run once per year.
delete from DateLookup
SET @Date = '1/1/' + convert(char(4), datepart("yyyy",getdate)-100)
and
WHILE @Date '1/1/' + convert(char(4),datepart(yy,getdate()) + 100)
one.
I used to use it for things like reporting production quantities over last shift, day, week etc.
Slightly different derivatives for durations as well.
It's especially handy with an outer join where you want to report 0s in a particular time range.
Trying to do that in code, SP or application can soon have you doing some extremely inefficient stuff, like select everything that isn't there...
I used to use it for things like reporting production quantities over last shift, day, week etc.
Slightly different derivatives for durations as well.
It's especially handy with an outer join where you want to report 0s in a particular time range.
Trying to do that in code, SP or application can soon have you doing some extremely inefficient stuff, like select everything that isn't there...
Very useful. I am busy setting up a data warehouse and a date dimension table was on my list of things to do. Thanks.
I'm not familiar with SQL but my company is considering using it in the near future, hence my interest.
The following code has got me scratching my head though:
SELECT WeekDayName, DayCount = COUNT(*)
FROM DateLookup
WHERE FullYear = 2003 AND
WeekDayName = 'Thursday'
GROUP BY WeekDayName
How does selecting WeekDayName of Thursday give you a count of all of the Wednesdays in 2003?
Hopefully someone will put me out of my misery.
The following code has got me scratching my head though:
SELECT WeekDayName, DayCount = COUNT(*)
FROM DateLookup
WHERE FullYear = 2003 AND
WeekDayName = 'Thursday'
GROUP BY WeekDayName
How does selecting WeekDayName of Thursday give you a count of all of the Wednesdays in 2003?
Hopefully someone will put me out of my misery.
That was the traditional stupid coding error so QA can gain job satisfaction from finding a bug. 
I thing you may end up with a count of all thursdays from that particular query.
I thing you may end up with a count of all thursdays from that particular query.
I was running through the code, so I could understand it better. I think this will help me with a DB I am working on.
When I ran the query for Wednesdays, it returned 53 instead of 52 ( The number for Thursdays ). Not sure it is a big deal, but I just in case you want to cross your t's and dot your i's.
When I ran the query for Wednesdays, it returned 53 instead of 52 ( The number for Thursdays ). Not sure it is a big deal, but I just in case you want to cross your t's and dot your i's.
It is an excellent and very helpful article. Saves hours of work. Best wishes for you.
Tariq Changgez
Tariq Changgez
Hi, Tim
I tried your code in mysql but receive following Error.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Date DATETIME
SET @Date = '1/1/1900'
WHILE @Date '1/1/2100' at line 1
Thank You...For Your Time
I tried your code in mysql but receive following Error.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Date DATETIME
SET @Date = '1/1/1900'
WHILE @Date '1/1/2100' at line 1
Thank You...For Your Time
The DatePart(ww,@Date) function also may misrepresent WeekNumber depending on your perspective.
1/1/2010 is a Friday and really the last "business week" of 2009 which starts on Sunday 12/27/2009 thru Sat 1/2/2010.
You may also need to ignore the fact that business work weeks often start on Monday thru Sunday, but that's another local altogether...
Other WorkWeek calculations can be local modifications to this tool.
1/1/2010 is a Friday and really the last "business week" of 2009 which starts on Sunday 12/27/2009 thru Sat 1/2/2010.
You may also need to ignore the fact that business work weeks often start on Monday thru Sunday, but that's another local altogether...
Other WorkWeek calculations can be local modifications to this tool.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
