Date Dimension - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date Dimension

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2012-07-02 : 01:32:37

Hi

i am very newly to tsql,here i having 2 date_dimension .
in that 1st date dimension will give calender date dimension,next one will give Fiscal date dimension,,i don't want to maintain separate 2 dimension for this how to merge this 2 date dimension and keep this as 1 dimension'..please help me for this tsql

-----------------------------------
DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2008-01-01', @EndDate = '2015-12-31'

WHILE (@StartDate <= @EndDate )
BEGIN
INSERT INTO [demo].[dbo].[DIM_DAY]
([DATE_SK]
,[TIME_DATE]
,[DAY_NUMBER]
,[DAY_NAME]
,[DAY_ABBREV]
,[LAST_DAY_IN_MONTH_FLAG]
,[MONTH_NUMBER]
,[MONTH_NAME]
,[MONTH_ABBREV]
,[QUARTER_NUMBER]
,[QUARTER_NAME]
,[QUARTER_YEAR]
,[YEAR_NUMBER]
,[YEAR_MONTH]
,[DAY_OF_YEAR]
,[WEEK_NUMBER]
,[WEEK_START_DATE]
,[WEEK_END_DATE]
,[DAY_OF_WEEK]
,[CREATED_DATETIME]
,[UPDATED_DATETIME]
)

SELECT
CAST(CONVERT(varchar(8), @StartDate ,112) AS int) DATESK
, @StartDate AS TIME_DATE
,DATENAME(DD, @StartDate) DAY_NUMBER
, UPPER(DATENAME(DW, @StartDate)) DAY_NAME
,SUBSTRING(upper(DATENAME(DW, @StartDate) ),1,3) AS DAY_ABBREV
,CASE
WHEN ( DATEADD(D, -DAY(DATEADD(M, 1, @StartDate )), DATEADD(M, 1, @StartDate ))= @StartDate )
THEN
'Y'
ELSE 'N'
END LAST_DAY_IN_MONTH_FLAG
, DATEPART(MM , @StartDate) MONTH_NUMBER
, UPPER(DATENAME(MM , @StartDate)) AS MONTH_NAME
, SUBSTRING (UPPER(DATENAME(MM , @StartDate)) ,1,3) MONTH_ABBREV
,DATEPART(QQ, @StartDate) QUARTER_NUMBER
,CASE DATEPART(QQ, @StartDate)
WHEN 1 THEN 'FIRST QUARTER'
WHEN 2 THEN 'SECOND QUARTER'
WHEN 3 THEN 'THIRD QUARTER'
WHEN 4 THEN 'FOURTH QUARTER'
END QUARTER_NAME
,datename(year, @StartDate)+''+datename(quarter, @StartDate) QUATER_YEAR
, DATEPART(YY , @StartDate) YEAR_NUMBER
,REPLACE(convert(varchar(7), @StartDate, 126),'-','') YEAR_MONTH
,DATENAME(DY, @StartDate) DAY_OF_YEAR
,DATEPART(WW, @StartDate) WEEK_NUMBER
,DATEADD(dd, -(DATEPART(dw, @StartDate)-1), @StartDate) WEEK_START_DATE
,DATEADD(dd, 7-(DATEPART(dw, @StartDate)), @StartDate) WEEK_END_DATE
,DATEPART(DW, @StartDate) DAY_OF_WEEK
, GETDATE () CREATED_DATETIME
, GETDATE() UPDATED_DATETIME
SET @StartDate = @StartDate +1
END
GO

-------------------------------------------------
Declare @MonthOffSet int
--If your Fiscal Year starts on October of the previous year then set this variable to 3 --------------------------
--If your Fiscal Year starts after the start of the calendar year set it to a negative number of months------------
Select @MonthOffSet = 9

--Declare all of the needed varables
DECLARE @StartDate datetime
, @EndDate datetime
, @Date datetime
, @MonthNumber numeric(20)
, @QuarterName varchar(6)
, @QuarterNumber numeric(20)
, @FirstFiscalDate date
, @FiscalYear numeric(10)

--Get first and last date
SELECT @StartDate = (Select Min([time_Date]) from [dim_day])
Select @EndDate = (Select Max([time_Date]) from [dim_day])
SELECT @Date = @StartDate

--set the first date of the fiscal year
SELECT @FirstFiscalDate = DATEADD(Month,-1*@MonthOffSet,@Date)

--Loop through each date
WHILE @Date <= @EndDate
BEGIN
--Set the number of months off set
Select @MonthNumber = Month(@Date) + @MonthOffSet
Select @MonthNumber =
Case When @MonthNumber > 12 then @MonthNumber - 12
When @MonthNumber < 1 then @MonthNumber + 12
Else @MonthNumber End

-- Set the Quarter off set
Select @QuarterNumber =
Case When @MonthNumber = 1 or @MonthNumber = 2 or @MonthNumber =3 Then 1
When @MonthNumber = 4 or @MonthNumber = 5 or @MonthNumber = 6 Then 2
When @MonthNumber = 7 or @MonthNumber = 8 or @MonthNumber = 9 Then 3
Else 4 End

Select @QuarterName =
Case @QuarterNumber
When 1
Then 'First'
When 2
Then 'Second'
When 3
Then 'Third'
When 4
Then 'Forth'
Else 'Error'
End

--Determine the fiscal year
Select @FiscalYear =
Case When MONTH(@date) < MONTH(@FirstFiscalDate) Then
(DATEPART(YEAR,@Date)-1)
Else (DATEPART(YEAR,@Date))
End

--Update the table with the fical numbers
Update dim_day
Set
FISCAL_MONTH_NUMBER = @MonthNumber,
FISCAL_QUARTER_NUMBER = @QuarterNumber,
FISCAL_QUARTER_NAME = upper(@QuarterName)+' QUARTER',
FISCAL_YEAR_NUMBER = @FiscalYear,
FISCAL_QUARTER_YEAR = convert (varchar(20), @FiscalYear )+ CAST(@QuarterNumber as varchar(50)),
FISCAL_YEAR_MONTH = convert (varchar(20), @FiscalYear )+ CAST(@MonthNumber as varchar(50))

Where time_Date = @Date
--Increment the date by one day
SELECT @Date = DATEADD(dd,1,@Date)
END

UPDATE DIM_DAY
SET

[FISCAL_DAY_OF_YEAR] =
DATEDIFF
(day,DATEADD(YEAR, DATEDIFF(MONTH, '19000401', TIME_DATE) / 12, '19000401')
,TIME_DATE+1
)

,[FISCAL_WEEK_NUMBER] = DATEDIFF
(week,DATEADD(YEAR, DATEDIFF(MONTH, '19000401', TIME_DATE) / 12, '19000401')
,TIME_DATE+7
)

anand

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-02 : 06:26:40

No need for two dimensions.
Each has a date as a key - I suggest the PK as integer yyyymmdd and the date as an attribute.
Everything else then becomes an attribute. Year, fiscal year, quarter, fiscal quarter, ...

To populate I use a number of ctes. The first generates the dates, next allocate top level attributes, next aything else you need. Then insert and do any final calculations to generate attributes. This makes the insert a single statement and should be easy to test.

Then you only need a single key in the fact table and just access the calendar or fiscal dates as needed.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources