SQL Date Time Bands SSAS Table

in what form? a table in sql server database? print on paper? something else?

I need it in a table.

we already have something like this.

      -- bandings
      SET @band2Hours = 'Between ' + CAST(@inHours - (@inHours % 2) AS VARCHAR) + ' and ' + CAST(1 + @inHours - (@inHours % 2) AS VARCHAR) + ' hours'
      SET @band3Hours = 'Between ' + CAST(@inHours - (@inHours % 3) AS VARCHAR) + ' and ' + CAST(2 + @inHours - (@inHours % 3) AS VARCHAR) + ' hours'
      SET @band4Hours = 'Between ' + CAST(@inHours - (@inHours % 4) AS VARCHAR) + ' and ' + CAST(3 + @inHours - (@inHours % 4) AS VARCHAR) + ' hours'
      SET @band5Hours = 'Between ' + CAST(@inHours - (@inHours % 5) AS VARCHAR) + ' and ' + CAST(4 + @inHours - (@inHours % 5) AS VARCHAR) + ' hours'
      SET @band10Hours = 'Between ' + CAST(@inHours - (@inHours % 10) AS VARCHAR) + ' and ' + CAST(9 + @inHours - (@inHours % 10) AS VARCHAR) + ' hours'
      SET @band12Hours = 'Between ' + CAST(@inHours - (@inHours % 12) AS VARCHAR) + ' and ' + CAST(11 + @inHours - (@inHours % 12) AS VARCHAR) + ' hours'
      SET @band24Hours = 'Between ' + CAST(@inHours - (@inHours % 24) AS VARCHAR) + ' and ' + CAST(23 + @inHours - (@inHours % 24) AS VARCHAR) + ' hours'
      SET @band36Hours = 'Between ' + CAST(@inHours - (@inHours % 36) AS VARCHAR) + ' and ' + CAST(35 + @inHours - (@inHours % 36) AS VARCHAR) + ' hours'
      SET @band48Hours = 'Between ' + CAST(@inHours - (@inHours % 48) AS VARCHAR) + ' and ' + CAST(47 + @inHours - (@inHours % 48) AS VARCHAR) + ' hours'
      SET @band72Hours = 'Between ' + CAST(@inHours - (@inHours % 72) AS VARCHAR) + ' and ' + CAST(71 + @inHours - (@inHours % 72) AS VARCHAR) + ' hours'

      INSERT INTO
      DimTimeBandHours (

thanks but its not what i needed.

I need to create a table so i can create a time calendar in SSAS.

I need to dynamically populate a table.

i have found this on google, but not quite correct.

CREATE TABLE [dbo].[DimTime](
 [TimeSK] [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_DimTime] PRIMARY KEY CLUSTERED 
 (
 [TimeSK] 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

PRINT CONVERT(VARCHAR,GETDATE(),113)--USED FOR CHECKING RUN TIME.

--Load time data for every second of a day
DECLARE @Time DATETIME

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

TRUNCATE TABLE DimTime

WHILE @TIME <= '11:59:59 PM'
 BEGIN
 INSERT INTO dbo.DimTime([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 DimTime
SET [HOUR] = '0' + [HOUR]
WHERE LEN([HOUR]) = 1

UPDATE DimTime
SET [MINUTE] = '0' + [MINUTE]
WHERE LEN([MINUTE]) = 1

UPDATE DimTime
SET [SECOND] = '0' + [SECOND]
WHERE LEN([SECOND]) = 1

UPDATE DimTime
SET [MilitaryHour] = '0' + [MilitaryHour]
WHERE LEN([MilitaryHour]) = 1

UPDATE DimTime
SET StandardTime = [Hour] + ':' + [Minute] + ':' + [Second] + ' ' + AmPm
WHERE StandardTime is null
AND HOUR <> '00'

UPDATE DimTime
SET StandardTime = '12' + ':' + [Minute] + ':' + [Second] + ' ' + AmPm
WHERE [HOUR] = '00'

Select allOpen in new window

If I understand correctly, this table is static table that you'd create and fill just once. You want to write a program for one-time filling 24 lines?

hello,

Yes that correct,

I need a static table to i can join my fact table to it.

The optimal way to create static table with 24 lines is to type them, or to use a trivial script like in my first comment.

If this is an academic assignment, the idea is that you have to do it by yourself. We can offer guidance, but we shouldn't be doing it for you.

No its not an assignment.

I left Uni many moons ago.
Since then i have Acquired a wife a few kids and a mortgage.

thanks vitor.