CREATE PROCEDURE [dbo].[Generate_dim_Tijd]

AS

BEGIN

------------------------------------------------------------------------------
--    Generate_dim_Tijd
------------------------------------------------------------------------------
--    Project:          SQL 2005 Datawarehouse                                         
--    Versie:           1.0                                                                                 
--    CreatieDatum:     6 oktokber 2009
--    Ontwikkelaar:     Ergens van internet gehaald door RK
--    Wijzigingen:     
-- EXAMPLE:
-- EXEC Generate_dim_Tijd
--
-- SELECT * FROM dim_Tijd

------------------------------------------------------------------------------



BEGIN TRY
    DROP TABLE [dim_Tijd]
END TRY
BEGIN CATCH
 --DO NOTHING
END CATCH
CREATE TABLE [dbo].[dim_Tijd]
    (
      [Tijd_ID] [int] IDENTITY(1, 1)
                 NOT NULL,
      [Tijd] [char](8) NOT NULL,
      [Uur] [char](2) NOT NULL,
      [24Uur] [char](2) NOT NULL,
      [Minuut] [char](2) NOT NULL,
      [Seconde] [char](2) NOT NULL,
      [AmPm] [char](2) NOT NULL,
      [StandaardTijd] [char](11) NULL,
      CONSTRAINT [PK_dim_Tijd] PRIMARY KEY CLUSTERED ( [Tijd_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]
 
-------------------------------------------------------------------------
------------Load time data for every second of a day---------------------

DECLARE @Time DATETIME

SET @TIME = CONVERT(VARCHAR, '12:00:00 AM', 108)

TRUNCATE TABLE dim_Tijd

WHILE @TIME <= '11:59:59 PM'
    BEGIN
        INSERT  INTO dbo.dim_Tijd
                (
                  [Tijd],
                  [Uur],
                  [24Uur],
                  [Minuut],
                  [Seconde],
                  [AmPm]
                )
                SELECT  CONVERT(VARCHAR, @TIME, 108) [Tijd],
                        CASE WHEN DATEPART(HOUR, @Time) > 12
                             THEN DATEPART(HOUR, @Time) - 12
                             ELSE DATEPART(HOUR, @Time)
                        END AS [Uur],
                        CAST(SUBSTRING(CONVERT(VARCHAR, @TIME, 108), 1, 2) AS INT) [24Uur],
                        DATEPART(MINUTE, @Time) [Minuut],
                        DATEPART(SECOND, @Time) [Seconde],
                        CASE WHEN DATEPART(HOUR, @Time) >= 12 THEN 'PM'
                             ELSE 'AM'
                        END AS [AmPm]

        SELECT  @TIME = DATEADD(second, 1, @Time)
    END

UPDATE  dim_Tijd
SET     [Uur] = '0' + [Uur]
WHERE   LEN([Uur]) = 1

UPDATE  dim_Tijd
SET     [Minuut] = '0' + [Minuut]
WHERE   LEN([Minuut]) = 1

UPDATE  dim_Tijd
SET     [Seconde] = '0' + [Seconde]
WHERE   LEN([Seconde]) = 1

UPDATE  dim_Tijd
SET     [24Uur] = '0' + [24Uur]
WHERE   LEN([24Uur]) = 1

UPDATE  dim_Tijd
SET     StandaardTijd = [Uur] + ':' + [Minuut] + ':' + [Seconde] + ' ' + [AmPm]
WHERE   StandaardTijd IS NULL
        AND [Uur] <> '00'

UPDATE  dim_Tijd
SET     StandaardTijd = '12' + ':' + [Minuut] + ':' + [Seconde] + ' ' + [AmPm]
WHERE   [Uur] = '00'

-------------------------------------------------------------------------
--------CREATE INDEXES---------------------------------------------------

CREATE UNIQUE NONCLUSTERED INDEX [IDX_dim_Tijd_Time] ON [dbo].[dim_Tijd] ( [Tijd] 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_Tijd_Hour] ON [dbo].[dim_Tijd] ( [Uur] 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_Tijd_MilitaryHour] ON [dbo].[dim_Tijd] ( [24Uur] 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_Tijd_Minute] ON [dbo].[dim_Tijd] ( [Minuut] 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_Tijd_Second] ON [dbo].[dim_Tijd] ( [Seconde] 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_Tijd_AmPm] ON [dbo].[dim_Tijd] ( [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_Tijd_StandardTime] ON [dbo].[dim_Tijd] ( [StandaardTijd] 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]

------------
--THE END

END