Post
Ask Question

Procedure for Creating Date Dimension and Populating it

  1. -----------CREATE TABLE DIM_DATE----------------DROP TABLE [DIM_DATE]  
  2. CREATE TABLE    [DBO].[DIM_DATE]  
  3. (   [DATESK] INT PRIMARY KEY,  
  4. [DATE] DATETIME,  
  5. [FULLDATECHAR] CHAR(10),-- DATE IN MM-DD-YYYY FORMAT  
  6. [DAYOFMONTH] VARCHAR(2), -- FIELD WILL HOLD DAY NUMBER OF MONTH  
  7. [DAYSUFFIX] VARCHAR(4), -- APPLY SUFFIX AS 1ST, 2ND ,3RD ETC  
  8. [DAYNAME] VARCHAR(9), -- CONTAINS NAME OF THE DAY, SUNDAY, MONDAY  
  9. [DAYOFWEEKUAE] CHAR(1),-- FIRST DAY SATURDAY=1 AND SUNDAY=7  
  10. [DAYOFWEEKINMONTH] VARCHAR(2), --1ST MONDAY OR 2ND MONDAY IN MONTH  
  11. [DAYOFWEEKINYEAR] VARCHAR(2),  
  12. [DAYOFQUARTER] VARCHAR(3),  
  13. [DAYOFYEAR] VARCHAR(3),  
  14. [WEEKOFMONTH] VARCHAR(1),-- WEEK NUMBER OF MONTH  
  15. [WEEKOFQUARTER] VARCHAR(2), --WEEK NUMBER OF THE QUARTER  
  16. [WEEKOFYEAR] VARCHAR(2),--WEEK NUMBER OF THE YEAR  
  17. [MONTH] VARCHAR(2), --NUMBER OF THE MONTH 1 TO 12  
  18. [MONTHNAME] VARCHAR(9),--JANUARY, FEBRUARY ETC  
  19. [MONTHOFQUARTER] VARCHAR(2),-- MONTH NUMBER BELONGS TO QUARTER  
  20. [QUARTER] CHAR(1),  
  21. [QUARTERNAME] VARCHAR(9),--FIRST,SECOND..  
  22. [YEAR] CHAR(4),-- YEAR VALUE OF DATE STORED IN ROW  
  23. [YEARNAME] CHAR(7), --CY 2012,CY 2013  
  24. [MONTHYEAR] CHAR(10), --JAN-2013,FEB-2013  
  25. [MMYYYY] CHAR(6),  
  26. [YYYYMM] int,  
  27. [FIRSTDAYOFMONTH] DATE,  
  28. [LASTDAYOFMONTH] DATE,  
  29. [FIRSTDAYOFQUARTER] DATE,  
  30. [LASTDAYOFQUARTER] DATE,  
  31. [FIRSTDAYOFYEAR] DATE,  
  32. [LASTDAYOFYEAR] DATE,  
  33. [DATEDISPLAY] VARCHAR(23),  
  34. [ISHOLIDAYUAE] BIT,-- FLAG 1=NATIONAL HOLIDAY, 0-NO NATIONAL HOLIDAY  
  35. [ISWEEKDAY] BIT,-- 0=WEEK END ,1=WEEK DAY  
  36. [HOLIDAYUAE] VARCHAR(50),--NAME OF HOLIDAY IN UAE  
  37. )  
  38. /********************************************************************************************/  
  39. --SPECIFY START DATE AND END DATE HERE  
  40. --VALUE OF START DATE MUST BE LESS THAN YOUR END DATE  
  41. DECLARE @STARTDATE DATETIME = '01/01/2009' --STARTING VALUE OF DATE RANGE  
  42. DECLARE @ENDDATE DATETIME = '01/01/2017' --END VALUE OF DATE RANGE  
  43. --TEMPORARY VARIABLES TO HOLD THE VALUES DURING PROCESSING OF EACH DATE OF YEAR  
  44. DECLARE  
  45. @DAYOFWEEKINMONTH INT,  
  46. @DAYOFWEEKINYEAR INT,  
  47. @DAYOFQUARTER INT,  
  48. @WEEKOFMONTH INT,  
  49. @CURRENTYEAR INT,  
  50. @CURRENTMONTH INT,  
  51. @CURRENTQUARTER INT  
  52. /*TABLE DATA TYPE TO STORE THE DAY OF WEEK COUNT FOR THE MONTH AND YEAR*/  
  53. DECLARE @DAYOFWEEK TABLE (DOW INT, MONTHCOUNT INT, QUARTERCOUNT INT, YEARCOUNT INT)  
  54. INSERT INTO @DAYOFWEEK VALUES (1, 0, 0, 0)  
  55. INSERT INTO @DAYOFWEEK VALUES (2, 0, 0, 0)  
  56. INSERT INTO @DAYOFWEEK VALUES (3, 0, 0, 0)  
  57. INSERT INTO @DAYOFWEEK VALUES (4, 0, 0, 0)  
  58. INSERT INTO @DAYOFWEEK VALUES (5, 0, 0, 0)  
  59. INSERT INTO @DAYOFWEEK VALUES (6, 0, 0, 0)  
  60. INSERT INTO @DAYOFWEEK VALUES (7, 0, 0, 0)  
  61. --EXTRACT AND ASSIGN VARIOUS PARTS OF VALUES FROM CURRENT DATE TO VARIABLE  
  62. DECLARE @CURRENTDATE AS DATETIME = @STARTDATE  
  63. SET @CURRENTMONTH = DATEPART(MM, @CURRENTDATE)  
  64. SET @CURRENTYEAR = DATEPART(YY, @CURRENTDATE)  
  65. SET @CURRENTQUARTER = DATEPART(QQ, @CURRENTDATE)  
  66. /********************************************************************************************/  
  67. --PROCEED ONLY IF START DATE(CURRENT DATE ) IS LESS THAN END DATE YOU SPECIFIED ABOVE  
  68. WHILE @CURRENTDATE < @ENDDATE  
  69. BEGIN  
  70. /*BEGIN DAY OF WEEK LOGIC*/  
  71. /*CHECK FOR CHANGE IN MONTH OF THE CURRENT DATE IF MONTH CHANGED THEN  
  72. CHANGE VARIABLE VALUE*/  
  73. IF @CURRENTMONTH != DATEPART(MM, @CURRENTDATE)  
  74. BEGIN  
  75. UPDATE @DAYOFWEEK  
  76. SET MONTHCOUNT = 0  
  77. SET @CURRENTMONTH = DATEPART(MM, @CURRENTDATE)  
  78. END  
  79. /* CHECK FOR CHANGE IN QUARTER OF THE CURRENT DATE IF QUARTER CHANGED THEN CHANGE  
  80. VARIABLE VALUE*/  
  81. IF @CURRENTQUARTER != DATEPART(QQ, @CURRENTDATE)  
  82. BEGIN  
  83. UPDATE @DAYOFWEEK  
  84. SET QUARTERCOUNT = 0  
  85. SET @CURRENTQUARTER = DATEPART(QQ, @CURRENTDATE)  
  86. END  
  87. /* CHECK FOR CHANGE IN YEAR OF THE CURRENT DATE IF YEAR CHANGED THEN CHANGE  
  88. VARIABLE VALUE*/  
  89. IF @CURRENTYEAR != DATEPART(YY, @CURRENTDATE)  
  90. BEGIN  
  91. UPDATE @DAYOFWEEK  
  92. SET YEARCOUNT = 0  
  93. SET @CURRENTYEAR = DATEPART(YY, @CURRENTDATE)  
  94. END  
  95. -- SET VALUES IN TABLE DATA TYPE CREATED ABOVE FROM VARIABLES  
  96. UPDATE @DAYOFWEEK  
  97. SET  
  98. MONTHCOUNT = MONTHCOUNT + 1,  
  99. QUARTERCOUNT = QUARTERCOUNT + 1,  
  100. YEARCOUNT = YEARCOUNT + 1  
  101. WHERE DOW = DATEPART(DW, @CURRENTDATE)  
  102. SELECT  
  103. @DAYOFWEEKINMONTH = MONTHCOUNT,  
  104. @DAYOFQUARTER = QUARTERCOUNT,  
  105. @DAYOFWEEKINYEAR = YEARCOUNT  
  106. FROM @DAYOFWEEK  
  107. WHERE DOW = DATEPART(DW, @CURRENTDATE)  
  108. /*END DAY OF WEEK LOGIC*/  
  109. /* POPULATE YOUR DIMENSION TABLE WITH VALUES*/  
  110. INSERT INTO [DBO].[DIM_DATE]  
  111. SELECT  
  112. CONVERT (CHAR(8),@CURRENTDATE,112) AS DATESK,  
  113. @CURRENTDATE AS DATE,  
  114. CONVERT (CHAR(10),@CURRENTDATE,101) AS FULLDATECHAR,  
  115. DATEPART(DD, @CURRENTDATE) AS DAYOFMONTH,  
  116. --APPLY SUFFIX VALUES LIKE 1ST, 2ND 3RD ETC..  
  117. CASE  
  118. WHEN DATEPART(DD,@CURRENTDATE) IN (11,12,13)  
  119. THEN CAST(DATEPART(DD,@CURRENTDATE) AS VARCHAR) + 'TH'  
  120. WHEN RIGHT(DATEPART(DD,@CURRENTDATE),1) = 1  
  121. THEN CAST(DATEPART(DD,@CURRENTDATE) AS VARCHAR) + 'ST'  
  122. WHEN RIGHT(DATEPART(DD,@CURRENTDATE),1) = 2  
  123. THEN CAST(DATEPART(DD,@CURRENTDATE) AS VARCHAR) + 'ND'  
  124. WHEN RIGHT(DATEPART(DD,@CURRENTDATE),1) = 3  
  125. THEN CAST(DATEPART(DD,@CURRENTDATE) AS VARCHAR) + 'RD'  
  126. ELSE CAST(DATEPART(DD,@CURRENTDATE) AS VARCHAR) + 'TH'  
  127. END AS DAYSUFFIX,  
  128. DATENAME(DW, @CURRENTDATE) AS DAYNAME,  
  129. -- CHECK FOR DAY OF WEEK AS PER US AND CHANGE IT AS PER UK FORMAT  
  130. DATEPART(DW, @CURRENTDATE)  
  131. AS DAYOFWEEKUAE,  
  132. @DAYOFWEEKINMONTH AS DAYOFWEEKINMONTH,  
  133. @DAYOFWEEKINYEAR AS DAYOFWEEKINYEAR,  
  134. @DAYOFQUARTER AS DAYOFQUARTER,  
  135. DATEPART(DY, @CURRENTDATE) AS DAYOFYEAR,  
  136. DATEPART(WW, @CURRENTDATE) + 1 - DATEPART(WW, CONVERT(VARCHAR,  
  137. DATEPART(MM, @CURRENTDATE)) + '/1/' + CONVERT(VARCHAR,  
  138. DATEPART(YY, @CURRENTDATE))) AS WEEKOFMONTH,  
  139. (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CURRENTDATE), 0),  
  140. @CURRENTDATE) / 7) + 1 AS WEEKOFQUARTER,  
  141. DATEPART(WW, @CURRENTDATE) AS WEEKOFYEAR,  
  142. DATEPART(MM, @CURRENTDATE) AS MONTH,  
  143. DATENAME(MM, @CURRENTDATE) AS MONTHNAME,  
  144. CASE  
  145. WHEN DATEPART(MM, @CURRENTDATE) IN (1, 4, 7, 10) THEN 1  
  146. WHEN DATEPART(MM, @CURRENTDATE) IN (2, 5, 8, 11) THEN 2  
  147. WHEN DATEPART(MM, @CURRENTDATE) IN (3, 6, 9, 12) THEN 3  
  148. END AS MONTHOFQUARTER,  
  149. DATEPART(QQ, @CURRENTDATE) AS QUARTER,  
  150. CASE DATEPART(QQ, @CURRENTDATE)  
  151. WHEN 1 THEN 'FIRST'  
  152. WHEN 2 THEN 'SECOND'  
  153. WHEN 3 THEN 'THIRD'  
  154. WHEN 4 THEN 'FOURTH'  
  155. END AS QUARTERNAME,  
  156. DATEPART(YEAR, @CURRENTDATE) AS YEAR,  
  157. 'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CURRENTDATE)) AS YEARNAME,  
  158. LEFT(DATENAME(MM, @CURRENTDATE), 3) + '-' + CONVERT(VARCHAR,  
  159. DATEPART(YY, @CURRENTDATE)) AS MONTHYEAR,  
  160. RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CURRENTDATE)),2) +  
  161. CONVERT(VARCHAR, DATEPART(YY, @CURRENTDATE)) AS MMYYYY,  
  162. CONVERT(VARCHAR(6),@CURRENTDATE,112) as YYYYMM,  
  163. CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,  
  164. @CURRENTDATE) - 1), @CURRENTDATE))) AS FIRSTDAYOFMONTH,  
  165. CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,  
  166. (DATEADD(MM, 1, @CURRENTDATE)))), DATEADD(MM, 1,  
  167. @CURRENTDATE)))) AS LASTDAYOFMONTH,  
  168. DATEADD(QQ, DATEDIFF(QQ, 0, @CURRENTDATE), 0) AS FIRSTDAYOFQUARTER,  
  169. DATEADD(QQ, DATEDIFF(QQ, -1, @CURRENTDATE), -1) AS LASTDAYOFQUARTER,  
  170. CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY,  
  171. @CURRENTDATE))) AS FIRSTDAYOFYEAR,  
  172. CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY,  
  173. @CURRENTDATE))) AS LASTDAYOFYEAR,  
  174. CONVERT(VARCHAR(16),@CURRENTDATE,103)+ ' '+CONVERT(VARCHAR(5),@CURRENTDATE,108) AS DATEDISPLAY,  
  175. NULL AS ISHOLIDAYUAE,  
  176. CASE DATEPART(DW, @CURRENTDATE)  
  177. WHEN 1 THEN 1  
  178. WHEN 2 THEN 1  
  179. WHEN 3 THEN 1  
  180. WHEN 4 THEN 1  
  181. WHEN 5 THEN 1  
  182. WHEN 6 THEN 0  
  183. WHEN 7 THEN 0  
  184. END AS ISWEEKDAY,  
  185. NULL AS HOLIDAYUAE  
  186. SET @CURRENTDATE = DATEADD(DD, 1, @CURRENTDATE)  
  187. END  
  188. /********************************************************************************************/  
  189. --STEP 3.UPDATE VALUES OF HOLIDAY AS PER UK GOVERNMENT DECLARATION FOR NATIONAL HOLIDAY.  
  190. /*UPDATE HOLIDAY FIELD OF UAE IN DIMENSION*/  
  191. /*THANKSGIVING - FOURTH THURSDAY IN NOVEMBER*/  
  192. UPDATE [DBO].[DIM_DATE]  
  193. SET HOLIDAYUAE = 'THANKSGIVING DAY'  
  194. WHERE  
  195. [MONTH] = 11  
  196. AND [DAYOFWEEKUAE] = 'THURSDAY'  
  197. AND DAYOFWEEKINMONTH = 4  
  198. /*CHRISTMAS*/  
  199. UPDATE [DBO].[DIM_DATE]  
  200. SET HOLIDAYUAE = 'CHRISTMAS DAY'  
  201. WHERE [MONTH] = 12 AND [DAYOFMONTH] = 25  
  202. /*4TH OF JULY*/  
  203. UPDATE [DBO].[DIM_DATE]  
  204. SET HOLIDAYUAE = 'INDEPENDANCE DAY'  
  205. WHERE [MONTH] = 7 AND [DAYOFMONTH] = 4  
  206. /*NEW YEARS DAY*/  
  207. UPDATE [DBO].[DIM_DATE]  
  208. SET HOLIDAYUAE = 'NEW YEAR''S DAY'  
  209. WHERE [MONTH] = 1 AND [DAYOFMONTH] = 1  
  210. /*****************************************************************************************/