Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Date and Time dimension creation and population T-SQL Expand / Collapse
Author
Message
Posted Sunday, February 08, 2009 1:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
Comments posted to this topic are about the item Date and Time dimension creation and population T-SQL
Post #652356
Posted Friday, February 13, 2009 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 06, 2013 8:07 AM
Points: 109, Visits: 489
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.
Post #656566
Posted Friday, February 13, 2009 9:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962
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.



ATB

Charles Kincaid

Post #656752
Posted Friday, February 13, 2009 9:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 06, 2013 8:07 AM
Points: 109, Visits: 489
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.
Post #656770
Posted Friday, February 13, 2009 10:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962
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?


ATB

Charles Kincaid

Post #656794
Posted Friday, February 13, 2009 11:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:30 PM
Points: 89, Visits: 1,047
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





Post #656908
Posted Friday, February 13, 2009 3:03 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid



  Post Attachments 
BuildDateTime.txt (47 views, 22.53 KB)
Post #657008
Posted Wednesday, February 18, 2009 1:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
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.
Post #659836
Posted Monday, March 30, 2009 1:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 4, Visits: 48
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.
Post #686412
Posted Tuesday, March 31, 2009 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
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...
Post #687025
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse