[펌] How to create a time dimension in SQL Server (시간 차원 만들기)

 

How to create a time dimension in SQL Server

 

In this post I’ll help you creating a Time Dimesnion as it is very much required if you are working on Sql Server Analysis Services. Until now you need to write a complex Sql procedure running into loops for creating a large Time Dimension. But with Sql Server 2005 and Common Table Expressions (CTE) it is very easy to create your own Time Dimension. A time Dimension is also called as Calendar Table by many.
When I started working on SSAS 2005 I also searched for a good Time Dimension, but now I have created my own Time Dimension with following Script.

Following script creates a table named TimeDiemension and populates it from 2000-01-01 to 2020-12-31. You can easily put your required dates.
Even though following query may look little big but believe me, it's nothing but a simple SELECT statement with CTE.
------- TimeDimension -------

WITH Mangal as
(
SELECT Cast ('2000-01-01' as DateTime) Date --Start Date
UNION ALL
SELECT Date + 1
FROM Mangal
WHERE Date + 1 < = '2020-12-31' --End date
)

SELECT Row_Number() OVER (ORDER BY Date) as DateId
,Date
,YEAR (date) as Year
,DatePart ( qq, date) as Quarter
,MONTH (date) as Month_Number_Year
,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as Month_Number_Of_Quarter
,DatePart (wk, Date) as Week_Number_Of_Year
,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as Week_Number_Of_Quarter
,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as Week_Number_Of_Month
,DatePart (dy, date) as Day_Number_Of_Year
,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 as Day_Number_Of_Quarter
,DAY (date) as Day_Number_Of_Month
,DatePart (dw, date) as Day_Number_Of_Week
,DateName (mm, date) as Month_Name
,LEFT ( DateName (mm, date), 3) Month_Name_Abbreviation
,DateName (dw, date) as Day_Name
,LEFT (DateName (dw, date), 3) as Day_Name_Abbreviation
,CONVERT(VarChar(10),date,112) as YYYYMMDD
,CONVERT(VarChar(10),date,111) as [YYYY/MM/DD]
,CONVERT(VarChar(11),date,100) as [mon dd yyyy]
,CONVERT(VarChar(11),date,120) as [yyyy-mm-dd]

INTO TimeDimension -- Name of the Table

FROM Mangal

OPTION (MAXRECURSION 0)

---- Script Ends Here ----
Now your TimeDimension is ready. Do a simple
SELECT * FROM TimeDimension
for a check.
Hopefully you will find this script helpful. Any questions and suggestions are welcome.
- Mangal Pardeshi.

출처 : http://mangalpardeshi.blogspot.kr/2008/12/how-to-create-time-dimension-in-sql.html