Date and Time dimension creation and population T-SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Joshua A. Walker » Date and Time dimension creation and...

23 posts, Page 1 of 3

1 2 3 »»»

Date and Time dimension creation and population T-SQL

Rate Topic

Display Mode

Topic Options

Author

Message

jwalker8680

Posted Sunday, February 08, 2009 1:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:28 PM
Points: 30, Visits: 110

Comments posted to this topic are about the item Date and Time dimension creation and population T-SQL

Post #652356

Cade Roux

Posted Friday, February 13, 2009 7:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:10 PM
Points: 105, Visits: 481

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

Charles Kincaid

Posted Friday, February 13, 2009 9:34 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

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

Cade Roux

Posted Friday, February 13, 2009 9:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:10 PM
Points: 105, Visits: 481

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

Charles Kincaid

Posted Friday, February 13, 2009 10:16 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

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

Chad Carter

Posted Friday, February 13, 2009 11:59 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:32 PM
Points: 87, Visits: 1,030

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

Charles Kincaid

Posted Friday, February 13, 2009 3:03 PM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

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 (40 views, 22.53 KB)

Post #657008

jwalker8680

Posted Wednesday, February 18, 2009 1:44 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:28 PM
Points: 30, Visits: 110

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

sang-lee

Posted Monday, March 30, 2009 1:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 2:37 PM
Points: 4, Visits: 46

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

jwalker8680

Posted Tuesday, March 31, 2009 7:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:28 PM
Points: 30, Visits: 110

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... Wink

Post #687025

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions