Poligon IT

W odmętach internetu znalazłem procedurę tworzącą tablicę, która posłuży nam do tworzenia hierarchii czasu w naszych kostkach.

set datefirst 1
declare
@start_date datetime
,@end_date datetime
,@loop_day datetime
,@diff int
,@loop int

Select
@start_date =’1950-01-01′ — Adjust as per need
,@end_date = ‘2050-12-31′
,@loop = 0

select
@diff = datediff(dd,@start_date,@end_date)

CREATE TABLE [Time_Dimension] (
[Time_ID] [int] identity(1,1) primary key,
[Cal_Date] [datetime] NULL ,
[The_Date] [int] NULL ,
[The_Day] [varchar] (20),
[The_Month_No] [int] NULL ,
[The_Year] [int] NULL ,
[The_Quarter] [varchar] (2),
) ON [PRIMARY]

while @loop <= @diff
begin
select @loop_day = dateadd(dd,@loop,@start_date)
insert into [Time_Dimension]
select
@loop_day
,datepart(dd,@loop_day)
,case datepart(dw,@loop_day)
when 1 then 'Poniedziałek'
when 2 then 'Wtorek'
when 3 then 'Środa'
when 4 then 'Czwartek'
when 5 then 'Piątek'
when 6 then 'Sobota'
when 7 then 'Niedziela'
end
,datepart(mm,@loop_day)
,datepart(yy,@loop_day)
,'Q'+convert(varchar,datepart(qq,@loop_day))

select
@loop = @loop + 1
end

Tags: olap, SQL Server 2005, time dimension

This entry was posted on Thursday, February 5th, 2009 at 13:09 and is filed under sql server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.