February 8, 2009 at 1:39 am
Comments posted to this topic are about the item Date and Time dimension creation and population T-SQL
February 13, 2009 at 7:23 am
Any particular reason you didn't use a natural int primary key of the form yyyymmdd?
We've found that to be pretty typical in Kimball-style DW designs, and it still leaves options for special int values.
You could even combine it with identity by applying the identity later or allowing identity insert.
February 13, 2009 at 9:34 am
Great article.
I was not able to copy and paste the code into SSMS. Had unicode characters and syntax errors that I could not see.
I also have lots of tables that have autonumber ID columns as the primary key. I have given up that practice. I query my item table mostly by item number rather than ID. My pallet table by pallet number. It makes sense to put my pages in order by the primary natural key. Change is hard.
ATBCharles Kincaid
February 13, 2009 at 9:47 am
jwalker8680 (2/13/2009)
Actually... "The Data Warehouse Toolkit Second Edition" by Ralph Kimball specifically mentions this on page 60, last paragraph.You should avoid using a readable date as the key, and instead use a surrogate key as some developers may use the readable format to bypass joining to the Date dimension.
Besides that... All of the other tables in my schemas have an autogenerated integer PK simply named [ID] and I prefer to keep it that way for consistancy.
That's odd. In the Microsoft Data Warehouse Toolkit on page 57, they say "Where other surrogate keys are usually a meaningless sequence of integers, it's a good idea to use a meaningful value for the Date surrogate key. Specifically, use an integer that corresponds to the date in year-month-day order... This can lead to more efficient queries against the relational database. It also makes implementing date-based partitioning much easier, and the partition management function will be more understandable".
They go on to state that sometimes they use smalldatetime instead of a role-played date dimension in some cases.
In particular, I like that the natural integer does let you do things like DATA_DT_ID BETWEEN START_DT_ID and END_DT_ID, which with a pure surrogate you have to express through the joins to the dimension table. This can make a huge difference in performance. I understand the point about developers not going through the dimension - if you can't trust your developers not to make date mistakes, that can be helpful.
February 13, 2009 at 10:16 am
That's better. Using a number that represents a date on some particular scale is more reasonable. It reminds me of the Unix time stamp. It also reminds me of something else. Hmm. Oh, I remember. It's the way SQL server STORES dates. It stores as a numeric value of the number of days and the fractional part is the time within the date. Therefore the date value is unique and meaningful. It uniquely identifies a particular row and is the result of an external authority or directly derived therefrom. Sounds like a grand basis for a primary key to me. You will only add to the end of the table and only insert missing values where that rare occasion occurs. Insert speed will be grand as that almost only happens on the link chain end and, as there are almost never mid-chain inserts, page splits will be minimal. You are not likely to modify key values.
This is sort of off the point though. The author posted a nice looking script and a well populated dates table is right handy. I'd love to be able to run the thing though. Where can I get it as ASCII text?
ATBCharles Kincaid
February 13, 2009 at 11:59 am
Here is the same code without the special characters.
--Create the tables
CREATE TABLE [dbo].[dim_Date](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NOT NULL,
[Day] [char](2) NOT NULL,
[DaySuffix] [varchar](4) NOT NULL,
[DayOfWeek] [varchar](9) NOT NULL,
[Month] [char](2) NOT NULL,
[MonthName] [varchar](9) NOT NULL,
[Quarter] [tinyint] NOT NULL,
[QuarterName] [varchar](6) NOT NULL,
[Year] [char](4) NOT NULL,
[StandardDate] [varchar](10) NULL,
[HolidayText] [varchar](50) NULL,
CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE TABLE [dbo].[dim_Time](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Time] [char](8) NOT NULL,
[Hour] [char](2) NOT NULL,
[MilitaryHour] [char](2) NOT NULL,
[Minute] [char](2) NOT NULL,
[Second] [char](2) NOT NULL,
[AmPm] [char](2) NOT NULL,
[StandardTime] [char](11) NULL,
CONSTRAINT [PK_dim_Time] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--Populate Date dimension --select count(*) from dim_date
print convert(varchar,getdate(),113)
truncate table dim_Date
DBCC CHECKIDENT (dim_Date, RESEED, 60000) --In case you need to add earlier dates later.
DECLARE @StartDate datetime
, @EndDate datetime
, @Date datetime
print getdate()
SELECT @StartDate = '1/1/1900'
, @EndDate = '1/1/2050'--Non inclusive. Stops on the day before this.
SELECT @Date = @StartDate
--select count(ID) from dim_Date --truncate table dim_Date
WHILE @Date < @EndDate
BEGIN
INSERT INTO dim_Date
(
[Date]
, [Day]
, [DaySuffix]
, [DayOfWeek]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName]
, [Year]
)
SELECT @Date [Date]
, DATEPART(DAY,@DATE) [Day]
, CASE
WHEN DATEPART(DAY,@DATE) IN (11,12,13) THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 1 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 2 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 3 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'
END AS [DaySuffix]
, CASE DATEPART(DW, @DATE)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END AS [DayOfWeek]
, DATEPART(MONTH,@DATE) [Month]
, DATENAME(MONTH,@DATE) [MonthName]
, DATEPART(qq,@DATE) [Quarter]
, CASE DATEPART(qq,@DATE)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS [QuarterName]
, DATEPART(YEAR,@Date) [Year]
SELECT @Date = DATEADD(dd,1,@Date)
END
UPDATE dbo.dim_Date
SET [DAY] = '0' + [DAY]
WHERE LEN([DAY]) = 1
UPDATE dbo.dim_Date
SET [MONTH] = '0' + [MONTH]
WHERE LEN([MONTH]) = 1
UPDATE dbo.dim_Date
SET STANDARDDATE = [MONTH] + '/' + [DAY] + '/' + [YEAR]
--Add HOLIDAYS --------------------------------------------------------------------------------------------------------------
--THANKSGIVING --------------------------------------------------------------------------------------------------------------
CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
TRUNCATE TABLE #tmpHoliday
INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])
SELECT [id], [YEAR], [DAY]
FROM dbo.dim_Date
WHERE [MONTH] = 11
AND [Dayofweek] = 'Thursday'
ORDER BY year, day
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT
SELECT @CURRENTYEAR = MIN([YEAR])
, @STARTYEAR = MIN([YEAR])
, @ENDYEAR = MAX([YEAR])
FROM #tmpHoliday
SELECT @CNTR, @POS, @STARTYEAR, @ENDYEAR, @CURRENTYEAR
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL
UPDATE #tmpHoliday
SET [WEEK] = @POS
WHERE [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE DT
SET HolidayText = 'Thanksgiving Day'
FROM dbo.dim_Date DT
JOIN #tmpHoliday HL
ON HL.DateID = DT.ID
WHERE [WEEK] = 4
DROP TABLE #tmpHoliday
GO
--CHRISTMAS -------------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET --IsHoliday = 1,
HolidayText = 'Christmas Day'
WHERE [MONTH] = 12 AND [DAY] = 25
--4th of July ---------------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET --IsHoliday = 1,
HolidayText = 'Independance Day'
WHERE [MONTH] = 7 AND [DAY] = 4
-- New Years Day ---------------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET --IsHoliday = 1,
HolidayText = 'New Year''s Day'
WHERE [MONTH] = 1 AND [DAY] = 1
--Memorial Day ----------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET --IsHoliday = 1,
HolidayText = 'Memorial Day'
FROM dim_Date
WHERE ID IN
(
SELECT MAX([ID])
FROM dbo.dim_Date
WHERE [MonthName] = 'May'
AND [DayOfWeek] = 'Monday'
GROUP BY [YEAR], [MONTH]
)
--Labor Day -------------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET --IsHoliday = 1,
HolidayText = 'Labor Day'
FROM dim_Date
WHERE ID IN
(
SELECT MIN([ID])
FROM dbo.dim_Date
WHERE [MonthName] = 'September'
AND [DayOfWeek] = 'Monday'
GROUP BY [YEAR], [MONTH]
)
-- Valentine's Day ---------------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET --IsHoliday = 1,
HolidayText = 'Valentine''s Day'
WHERE [MONTH] = 2 AND [DAY] = 14
-- Saint Patrick's Day -----------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET --IsHoliday = 1,
HolidayText = 'Saint Patrick''s Day'
WHERE [MONTH] = 3 AND [DAY] = 17
--Martin Luthor King Day ---------------------------------------------------------------------------------------
BEGIN TRY
drop table #tmpHoliday
END TRY
BEGIN CATCH
--do nothing
end catch
CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
TRUNCATE TABLE #tmpHoliday
INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])
SELECT [id], [YEAR], [DAY]
FROM dbo.dim_Date
WHERE [MONTH] = 1
AND [Dayofweek] = 'Monday'
AND [YEAR] >= 1983
ORDER BY year, day
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT
SELECT @CURRENTYEAR = MIN([YEAR])
, @STARTYEAR = MIN([YEAR])
, @ENDYEAR = MAX([YEAR])
FROM #tmpHoliday
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL
UPDATE #tmpHoliday
SET [WEEK] = @POS
WHERE [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE DT
SET HolidayText = 'Martin Luthor King Jr Day'
FROM dbo.dim_Date DT
JOIN #tmpHoliday HL
ON HL.DateID = DT.ID
WHERE [WEEK] = 3
DROP TABLE #tmpHoliday
--UPDATE dim_Date SET HOLIDAYTEXT = NULL WHERE HOLIDAYTEXT = 'Martin Luthor King Day' and year < 1983
GO
--President's Day ---------------------------------------------------------------------------------------
BEGIN TRY
drop table #tmpHoliday
END TRY
BEGIN CATCH
--do nothing
end catch
CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
TRUNCATE TABLE #tmpHoliday
INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])
SELECT [id], [YEAR], [DAY]
FROM dbo.dim_Date
WHERE [MONTH] = 2
AND [Dayofweek] = 'Monday'
ORDER BY year, day
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT
SELECT @CURRENTYEAR = MIN([YEAR])
, @STARTYEAR = MIN([YEAR])
, @ENDYEAR = MAX([YEAR])
FROM #tmpHoliday
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL
UPDATE #tmpHoliday
SET [WEEK] = @POS
WHERE [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE DT
SET --IsHoliday = 1,
HolidayText = 'President''s Day'
FROM dbo.dim_Date DT
JOIN #tmpHoliday HL
ON HL.DateID = DT.ID
WHERE [WEEK] = 3
DROP TABLE #tmpHoliday
GO
--Mother's Day ---------------------------------------------------------------------------------------
BEGIN TRY
drop table #tmpHoliday
END TRY
BEGIN CATCH
--do nothing
end catch
CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
--drop table #tmpHoliday
TRUNCATE TABLE #tmpHoliday
INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])
SELECT [id], [YEAR], [DAY]
FROM dbo.dim_Date
WHERE [MONTH] = 5
AND [Dayofweek] = 'Sunday'
ORDER BY year, day
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT
SELECT @CURRENTYEAR = MIN([YEAR])
, @STARTYEAR = MIN([YEAR])
, @ENDYEAR = MAX([YEAR])
FROM #tmpHoliday
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL
UPDATE #tmpHoliday
SET [WEEK] = @POS
WHERE [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE DT
SET HolidayText = 'Mother''s Day'
FROM dbo.dim_Date DT
JOIN #tmpHoliday HL
ON HL.DateID = DT.ID
WHERE [WEEK] = 2
DROP TABLE #tmpHoliday
GO
--Father's Day ---------------------------------------------------------------------------------------
BEGIN TRY
drop table #tmpHoliday
END TRY
BEGIN CATCH
--do nothing
end catch
CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
TRUNCATE TABLE #tmpHoliday
INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])
SELECT [id], [YEAR], [DAY]
FROM dbo.dim_Date
WHERE [MONTH] = 6
AND [Dayofweek] = 'Sunday'
ORDER BY year, day
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT
SELECT @CURRENTYEAR = MIN([YEAR])
, @STARTYEAR = MIN([YEAR])
, @ENDYEAR = MAX([YEAR])
FROM #tmpHoliday
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL
UPDATE #tmpHoliday
SET [WEEK] = @POS
WHERE [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE DT
SET HolidayText = 'Father''s Day'
FROM dbo.dim_Date DT
JOIN #tmpHoliday HL
ON HL.DateID = DT.ID
WHERE [WEEK] = 3
DROP TABLE #tmpHoliday
GO
--Halloween 10/31 ----------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET HolidayText = 'Halloween'
WHERE [MONTH] = 10 AND [DAY] = 31
--Election Day--------------------------------------------------------------------------------------
--The first Tuesday after the first Monday in November.
BEGIN TRY
drop table #tmpHoliday
END TRY
BEGIN CATCH
--do nothing
end catch
CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
TRUNCATE TABLE #tmpHoliday
INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])
SELECT [id], [YEAR], [DAY]
FROM dbo.dim_Date
WHERE [MONTH] = 11
AND [Dayofweek] = 'Monday'
ORDER BY year, day
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT
SELECT @CURRENTYEAR = MIN([YEAR])
, @STARTYEAR = MIN([YEAR])
, @ENDYEAR = MAX([YEAR])
FROM #tmpHoliday
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL
UPDATE #tmpHoliday
SET [WEEK] = @POS
WHERE [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE DT
SET HolidayText = 'Election Day'
FROM dbo.dim_Date DT
JOIN #tmpHoliday HL
ON (HL.DateID + 1) = DT.ID
WHERE [WEEK] = 1
DROP TABLE #tmpHoliday
GO
--------------------------------------------------------------------------------------------------------
print convert(varchar,getdate(),113)
--Load time data for every second of a day
DECLARE @Time DATETIME
SET @TIME = CONVERT(VARCHAR,'12:00:00 AM',108)
SELECT @TIME
SELECT CONVERT(VARCHAR,@TIME,108)
TRUNCATE TABLE dim_Time
WHILE @TIME <= '11:59:59 PM'
BEGIN
INSERT INTO dbo.dim_Time([Time], [Hour], [MilitaryHour], [Minute], [Second], [AmPm])
SELECT CONVERT(VARCHAR,@TIME,108) [Time]
, CASE
WHEN DATEPART(HOUR,@Time) > 12 THEN DATEPART(HOUR,@Time) - 12
ELSE DATEPART(HOUR,@Time)
END AS [Hour]
, CAST(SUBSTRING(CONVERT(VARCHAR,@TIME,108),1,2) AS INT) [MilitaryHour]
, DATEPART(MINUTE,@Time) [Minute]
, DATEPART(SECOND,@Time) [Second]
, CASE
WHEN DATEPART(HOUR,@Time) >= 12 THEN 'PM'
ELSE 'AM'
END AS [AmPm]
SELECT @TIME = DATEADD(second,1,@Time)
END
UPDATE dim_Time
SET [HOUR] = '0' + [HOUR]
WHERE LEN([HOUR]) = 1
UPDATE dim_Time
SET [MINUTE] = '0' + [MINUTE]
WHERE LEN([MINUTE]) = 1
UPDATE dim_Time
SET [SECOND] = '0' + [SECOND]
WHERE LEN([SECOND]) = 1
UPDATE dim_Time
SET StandardTime = [Hour] + ':' + [Minute] + ':' + [Second] + ' ' + AmPm
WHERE StandardTime is null
AND HOUR <> '00'
UPDATE dim_Time
SET StandardTime = '12' + ':' + [Minute] + ':' + [Second] + ' ' + AmPm
WHERE [HOUR] = '00'
--dim_date indexes
CREATE UNIQUE NONCLUSTERED INDEX [IDX_dim_Date_Date] ON [dbo].[dim_Date]
(
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Date_Day] ON [dbo].[dim_Date]
(
[Day] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Date_DayOfWeek] ON [dbo].[dim_Date]
(
[DayOfWeek] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Date_Month] ON [dbo].[dim_Date]
(
[Month] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Date_MonthName] ON [dbo].[dim_Date]
(
[MonthName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Date_Quarter] ON [dbo].[dim_Date]
(
[Quarter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Date_QuarterName] ON [dbo].[dim_Date]
(
[QuarterName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Date_Year] ON [dbo].[dim_Date]
(
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_HolidayText] ON [dbo].[dim_Date]
(
[HolidayText] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
--dim_Time indexes
CREATE UNIQUE NONCLUSTERED INDEX [IDX_dim_Time_Time] ON [dbo].[dim_Time]
(
[Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_Hour] ON [dbo].[dim_Time]
(
[Hour] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_MilitaryHour] ON [dbo].[dim_Time]
(
[MilitaryHour] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_Minute] ON [dbo].[dim_Time]
(
[Minute] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_Second] ON [dbo].[dim_Time]
(
[Second] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_AmPm] ON [dbo].[dim_Time]
(
[AmPm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_StandardTime] ON [dbo].[dim_Time]
(
[StandardTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
--USAGE EXAMPLES:
--Join date and time and give all records for a specific date and time.--------------------
DECLARE @DATETIME DATETIME
SET @DATETIME = '07/30/1976 4:01:02 PM'
SELECT [DiffDays] = DATEDIFF(dd,[DATE],GETDATE())
, [DiffYears] = DATEDIFF(dd,[DATE],GETDATE()) / 365.242199
,*, [StandardDateTime] = STANDARDDATE + ' ' + STANDARDTIME
FROM dim_Date DT
JOIN dim_Time TM
ON TM.TIME = CONVERT(VARCHAR,@DATETIME,108)
WHERE DATE = CONVERT(VARCHAR,@DATETIME,101)
-------------------------------------------------------------------------------------------
--GET MONTH AND YEAR WHERE IT HAS MORE THAN 4 FRIDAYS
SELECT Month, Year, COUNT(DAY)
FROM dim_Date
WHERE DAYOFWEEK = 'FRIDAY'
AND YEAR IN (2008, 2009)
GROUP BY MONTH, YEAR
HAVING COUNT(DAY) > 4
ORDER BY YEAR, MONTH
--Get the number of days per year.
SELECT YEAR, COUNT(DAY) [days]
FROM dim_Date
GROUP BY YEAR
--Get paydays where 1/2/2009 is a payday.
SELECT CAST((DATEDIFF(dd,'1/2/2009',DATE) / 14.00) AS VARCHAR) [DiffFromStart], *
FROM dim_Date
WHERE DAYOFWEEK = 'Friday'
AND DATE >= '1/2/2001'--Starting at this date
AND (DATEDIFF(dd,'1/2/2009',DATE) / 14.0) = ROUND((DATEDIFF(dd,'1/2/2009',DATE) / 14.0),0)
--Month and year where we get three paydays in one month from 2009 on...
SELECT MONTH, YEAR
FROM dim_Date
WHERE DATE >= '1/1/2009'
AND (DATEDIFF(dd,'1/2/2009',DATE) / 14.0) = ROUND((DATEDIFF(dd,'1/2/2009',DATE) / 14.0),0)
GROUP BY MonthName, Month, Year
HAVING COUNT(DAY) >= 3
ORDER BY YEAR, MONTH
February 13, 2009 at 3:03 pm
Thanks. I had to go into view source and grab it. No big thing that. Then I had to turn all the non-breaking-space codes into spaces, all the gt and lt markers back into greaters and lessers. Some of the right parens got turned into wink icons. 😉 What is with the semi-colons? Oh and there was one non-breaking-space that did not have the trailing semicolon.
I have attached the cleaned up version as a text file. Let's see if that works.
ATBCharles Kincaid
February 18, 2009 at 1:44 pm
To simplify a few queries and add a little more functionality I have added a few new columns. [DOWInMonth] is the occurrence of that Day-of-Week in the month.
EX: 2/14/09 is the second Saturday in February so the value of DOWInMonth is 2.
Additionally there is a [WeekOfYear] and a [WeekOfMonth] column. Those should be obvious, but they are indicators of the number of the week as related to the month and year.
The [DayOfYear] column is the day as related to the year. 0 - 365/366.
I have also made the default ID for the dim_Date table the integer value of the date in YYYYMMDD format. If you wish to do as Kimball suggest and make this a non-date integer you can uncomment/comment the appropriate lines. All the code was left in place.
March 30, 2009 at 1:04 pm
How would you change this script to account for a shift in calendar dates. For example, if a reporting calendar begins on the 19th of every month? (e.g. 01/18/2009 = Month 12, Day 31, Year 2008 and 01/19/2009 = Month 1, Day 1, Year 2009)? I've been able to script most of it. Where I seem to have trouble is in addition accounting for the first day of the week begin something toher than the default. For example if the first day of the month is the 19th, and the first days of the week is a friday, then 1/19/2009 is Month 1, Day 1, Year 2009, but week 53 of 2008 because the first week has not started yet for 2009.
March 31, 2009 at 7:34 am
Uh... dunno...
Not sure why you would want the first of the month to be the 19th... or vice versa...
I'm sure you have good reason for it... but I'm sure there would be a lot of manual coding to modify the default data that is put in my tables.
My suggestion... Load them from the code that is posted... and run update statements on your own calculations... good luck... 😉
March 31, 2009 at 7:59 am
This is a wonderful bit of code. Pity I didn't find it about 4 hrs ago 🙂 . Just 1 thing. If I want the WeekOfMonth to start on a Monday, Where and how do I need to alter the code. I can see it is somewhere in lines 142 , 143 "
, DATEPART(ww,@Date) + 0 - -----changed to set Monday as Day 1
DATEPART(ww,CAST(DATEPART(mm,@Date) AS VARCHAR) + '/1/' + CAST(DATEPART(yy,@Date) AS VARCHAR)) [WeekOfMonth]"
and as you can see I have tried with amending the + but no luck.
What I am trying to achieve is having the week of Month set to 1 for the first full week of the Mmonth starting from the fisrt Monday.
Once again, excellent code and please advise.
Nick
March 31, 2009 at 8:16 am
Excellent code. Wish I had found this several hours back. Can you advise how to amend this so week of month starts on Moday as opposed to Sunday.
Many thanks
Nick
April 9, 2009 at 7:30 am
Dunno really what to make of this script. I'll have to take time and go through it some more but I like the idea of setting up tables with holidays but unfortunately every country's holidays is different so a lot of editing will be required.:cool:
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 22, 2009 at 10:30 am
These are great!! Thanks for sharing the scripts.
April 23, 2009 at 8:23 am
The code for figuring out the week of the month is built in SQL functionality... I don't know that there is a way, non-manual, to calculate week of month if the week starts on Monday instead of Sunday...
I'm sure you can do it by putting in a loop and checking day one... and counting the days from there... etc... but that would take a lot of code and a lot of time to loop through every day for 150 years...
Yeah... it was a bit of work getting it to this point and I wish someone had done it like this before me to save me all that time... but I figured I'd save everyone else a bit of time... 😉
Your Welcome... Your Welcome... Your Welcome...
PS>... For the holiday's comment... yeah... Those are mostly manual as you can see in the code... pick and choose your own... and if anyone has a way of programatically calculating when the Jewish calender holidays are...etc... I'd love to see it and add it to this code for everyone.
As for easter... I found the code posted by
AUTHOR: Robert Davis
SOURCE: http://www.databasejournal.com/scripts/article.php/3469911/Calculate-Easter-for-Any-Year.htm
Add this code to the script I posted after you add the function from the link above.
--Easter ------------------------------------------------------------------------------------------------------
DECLARE @Years TABLE([ID] INT IDENTITY(1,1), [YEAR][INT])
INSERT INTO @YEARS([YEAR])
SELECT DISTINCT YEAR
FROM [dim_DATE]
DECLARE @POS BIGINT, @CNTR BIGINT, @YEAR VARCHAR(4)
SELECT @POS = 1, @CNTR = MAX([ID])
FROM @YEARS
WHILE @POS <= @CNTR
BEGIN
SELECT @YEAR = [YEAR]
FROM @YEARS
WHERE ID = @POS
UPDATE dim_DATE
SET HolidayText = ISNULL(HolidayText,'') + 'Easter'
WHERE DATE = CAST(dbo.Year2Easter(@YEAR) AS DATETIME)
SELECT @POS = @POS + 1
END
GO
------------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply