BI | BI‎ > ‎

BI.tsql | Create Dim Datetime

CREATE TABLE [dbo].[DIM_DATETIME](
[ID] [bigint] NULL,
[DT] [datetime2](0) NULL
) ON [ps_PT_DIM](DT)
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCI] ON [dbo].[DIM_DATETIME] WITH (DROP_EXISTING = OFF)
GO

;with a as (select row_number() over (order by b1) c from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a1 (b1)
cross join  (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a2 (b2))
, b as (select row_number() over (order by a1.c) r from a a1 cross join a a2 cross join a a3)
insert into [dbo].[DIM_DATETIME]
select r, DATEADD(day, r, '19491219') from b where r < 28000
GO

Comments