מימד זמן-סקריפטים

SQL Server:
--select * from [dbo].[DateDim] where month=0

--drop table [dbo].[DateDim]

go

CREATE TABLE [dbo].[DateDim] (
	[DateKey] [int]  NULL ,
	[ActualDate] [datetime]  NULL ,
	[Year] [int]  NULL ,
	[Quarter] [int]  NULL ,
	[Month] [int]  NULL ,
	[Week] [int]  NULL ,
	[DayofYear] [int]  NULL ,
	[DayofMonth] [int] NULL ,
	[DayofWeek] [int] NULL ,
	[IsWeekend] [bit]  NULL ,
	[IsHoliday] [bit]  NULL ,
	[Comments] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
	[CalendarWeek] [int]  NULL ,
	[BusinessYearWeek] [int]  NULL ,
	[LeapYear] [tinyint]  NULL ,
    [year_month] [varchar] (8)NULL,
    [year_quarter] [varchar](8) null
) ON [PRIMARY]
GO

--default NULL value

INSERT DateDim  (ActualDate, Year, Quarter, Month, Week, DayofYear, DayofMonth, DayofWeek, IsWeekend, CalendarWeek, BusinessYearWeek, LeapYear,year_month,year_quarter)
values ('1/1/1920',0,0,0,0,0,0,0,0,0,0,0,'Unknown','Unknown')



--drop procedure sp_createTimeDim
go

CREATE PROCEDURE sp_createTimeDim  AS

--delete contents of Date Dimension Table
TRUNCATE TABLE DateDim
--select * from DateDim
--declare variables
DECLARE @DT DATETIME
DECLARE @YEAR INT
DECLARE @QUARTER INT
DECLARE @MONTH  INT
DECLARE @WEEK  INT
DECLARE @DayofYear INT
DECLARE @DayofMonth INT
DECLARE @DayofWeek INT
DECLARE @IsWeekend  BIT
DECLARE @IsHoliday  BIT
DECLARE @CalendarWeek INT
DECLARE @DayName VARCHAR(20)
DECLARE @MonthName VARCHAR(20)
DECLARE @BusinessYearWeek INT
DECLARE @LeapYear BIT

--initialize variables

SELECT @BusinessYearWeek =0
SELECT @CalendarWeek = 1
SELECT @LeapYear =0

--the starting date for the date dimension
SELECT @DT  = '1/1/1988'

--start looping, stop at ending date
WHILE (@DT <= '1/31/2015')
BEGIN

--get information about the data
	SELECT @IsWeekend  =0
	SELECT @YEAR = DATEPART (YEAR, @DT)
	SELECT @QUARTER = DATEPART (QUARTER, @DT) 
	SELECT @MONTH = DATEPART (MONTH , @DT)
	SELECT @WEEK  = DATEPART (WEEK , @DT)
	SELECT @DayofYear   = DATEPART (DY , @DT)
	SELECT @DayofMonth   = DATEPART (DAY , @DT)
	SELECT @DayofWeek   = DATEPART (DW , @DT)

--note if weeknd or not
IF ( @DayofWeek = 1 OR  @DayofWeek = 7 )  
BEGIN
	SELECT @IsWeekend   = 1
END

--add 1 every time we start a new week
IF ( @DayofWeek = 1)
BEGIN
	SELECT @CalendarWeek = @CalendarWeek +1
END

--add business rule (need to know complete weeks in a year, so a partial week in new year set to 0)
IF ( @DayofWeek != 1 AND @DayofYear = 1)
BEGIN
	SELECT @BusinessYearWeek = 0
END


IF ( @DayofWeek = 1)
BEGIN
	SELECT @BusinessYearWeek = @BusinessYearWeek +1
END

--add business rule (start counting business weeks with first complete week)
IF (@BusinessYearWeek =53)
BEGIN
	SELECT @BusinessYearWeek = 1
END

--check for leap year
IF ((@YEAR % 4 = 0)  AND (@YEAR % 100 != 0 OR @YEAR % 400 = 0))
	SELECT @LeapYear =1
	ELSE SELECT @LeapYear =0

--insert values into Date Dimension table

INSERT DateDim  (ActualDate, Year, Quarter, Month, Week, DayofYear, DayofMonth, DayofWeek, IsWeekend, CalendarWeek, BusinessYearWeek, LeapYear,year_month,year_quarter)
 VALUES (@DT, @YEAR, @QUARTER, @MONTH, @WEEK, @DayofYear, @DayofMonth, @DayofWeek, @IsWeekend, @CalendarWeek, @BusinessYearWeek, @LeapYear,convert(varchar,@year)+'\'+right('0'+convert(varchar,@month),2),convert(varchar,@year)+'\'+right('Q'+convert(varchar,@quarter),2) )

--increment the date one day
SELECT @DT  = DATEADD(DAY, 1, @DT)

END
GO

exec sp_createTimeDim
Oracle: בהמשך
BI&BigData

מימד זמן-סקריפטים
