Date and Time dimension creation and population T-SQL

  • jwalker8680

    SSC Eights!

    Points: 858

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

  • Cade Roux

    Ten Centuries

    Points: 1326

    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.

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • Cade Roux

    Ten Centuries

    Points: 1326

    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.

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • Chad Carter

    Ten Centuries

    Points: 1089

    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

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • jwalker8680

    SSC Eights!

    Points: 858

    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.

  • sang-lee

    SSC Enthusiast

    Points: 104

    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.

  • jwalker8680

    SSC Eights!

    Points: 858

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

  • applebynick

    Mr or Mrs. 500

    Points: 542

    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

  • applebynick

    Mr or Mrs. 500

    Points: 542

    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

  • Manie Verster

    SSCertifiable

    Points: 7020

    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)

  • jonathan.despain

    SSC Enthusiast

    Points: 108

    These are great!! Thanks for sharing the scripts.

  • jwalker8680

    SSC Eights!

    Points: 858

    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 25 total)

You must be logged in to reply to this topic. Login to reply