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