USP_POPULATETIMEDIM

create procedure BBDW.[USP_POPULATETIMEDIM] as

declare @TIME datetime
set @TIME = convert(varchar, '12:00:00 AM', 108)

if (select count(1) from BBDW.[DIM_TIME]) <> 1441

  begin

    truncate table BBDW.[DIM_TIME]

    begin

      set identity_insert BBDW.[DIM_TIME] on

      insert into BBDW.[DIM_TIME]([TIMEDIMID], [TIME], [TIMESTRING], [HOUR], [HOURSTRING], [MILITARYHOUR], [MILITARYHOURSTRING], [MINUTE], [MINUTESTRING], [AMPM], [AMPMCODE], [STANDARDTIME], [MILITARYTIME], [ETLCONTROLID], [SOURCEDIMID], [ISINCLUDED])
        select
          0,
          null,
          '',
          0,
          '',
          0,
          '',
          0,
          '',
          '',
          0,
          '',
          '',
          0,
          1,
          0

      set identity_insert BBDW.[DIM_TIME] off

    end

    while @TIME <= '11:59:59 PM'

     begin

       insert into BBDW.[DIM_TIME]([TIME], [HOUR], [MILITARYHOUR], [MINUTE], [AMPM], [AMPMCODE], [ETLCONTROLID], [SOURCEDIMID], [ISINCLUDED])
         select 
           convert(time, @TIME, 108) as  [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 tinyint) [MILITARYHOUR],
           datepart(minute, @TIME) [MINUTE],
           case 
            when datepart(hour, @TIME) >= 12 then 'PM'
            else 'AM'
            end as [AMPM],
           case 
            when datepart(hour, @TIME) >= 12 then 1
            else 0
            end as [AMPMCODE],
           0 as [ETLCONTROLID],
           1 as [SOURCEDIMID],
           1 as [ISINCLUDED];

       select @TIME = dateadd(minute, 1, @TIME)

     end

    update BBDW.[DIM_TIME]
      set 
        [HOURSTRING] = 
          case when [HOUR] <> '00'
            then cast([HOUR] as nvarchar(2))
            else '12'
          end,
        [MINUTESTRING] = 
          case when len(cast([MINUTE] as nvarchar(2))) = 2
            then cast([MINUTE] as nvarchar(2))
            else '0' + cast([MINUTE] as nvarchar(2))
            end,
        [MILITARYHOURSTRING] = 
          case when len(cast([MILITARYHOUR] as nvarchar(2))) = 2
          then cast([MILITARYHOUR] as nvarchar(2))
          else '0' + cast([MILITARYHOUR] as nvarchar(2))
          end
    where [TIMEDIMID] > 0;

    update BBDW.[DIM_TIME]
      set 
        [TIMESTRING] = 
          case when len([HOURSTRING]) = 1 
            then '0' + cast([HOURSTRING] as nchar(1))+ [MINUTESTRING]
            else [HOURSTRING] + [MINUTESTRING]
            end,
        [STANDARDTIME] = rtrim([HOURSTRING]) + ':' + [MINUTESTRING] + ' ' + [AMPM],
        [MILITARYTIME] = [MILITARYHOURSTRING] + ':' + [MINUTESTRING]
    where [TIMEDIMID] > 0;

  end;