Tạo bảng Dimentions Date on SQL

 Việc tạo bảng Dim_date phân tích dữ liệu và xây dựng báo cáo  trong các BI cực kỳ quan trọng. 

dưới đây là 2 câu lệnh mình sưu tầm được tạo nhanh bảng Date dưới SQL phục vụ cho công việc.

Tạo bảng Dim date trong sql

full code generate :

/* ACB */

CREATE TABLE dbo.Dim_Date (

   [DateKey] INT NOT NULL PRIMARY KEY,

   [Date] DATE NOT NULL,

   [Day] TINYINT NOT NULL,

   [DaySuffix] CHAR(2) NOT NULL,

   [Weekday] TINYINT NOT NULL,

   [WeekDayName] VARCHAR(10) NOT NULL,

   [WeekDayName_Short] CHAR(3) NOT NULL,

   [WeekDayName_FirstLetter] CHAR(1) NOT NULL,

   [DOWInMonth] TINYINT NOT NULL,

   [DayOfYear] SMALLINT NOT NULL,

   [WeekOfMonth] TINYINT NOT NULL,

   [WeekOfYear] TINYINT NOT NULL,

   [Month] TINYINT NOT NULL,

   [MonthName] VARCHAR(10) NOT NULL,

   [MonthName_Short] CHAR(3) NOT NULL,

   [MonthName_FirstLetter] CHAR(1) NOT NULL,

   [Quarter] TINYINT NOT NULL,

   [QuarterName] VARCHAR(6) NOT NULL,

   [Year] INT NOT NULL,

   [MMYYYY] CHAR(6) NOT NULL,

   [MonthYear] CHAR(7) NOT NULL,

   [IsWeekend] BIT NOT NULL,

   [IsHoliday] BIT NOT NULL,

   

   )

-- Tự động  Generate dữ liệu dữ liệu dimension_date

   SET NOCOUNT ON

TRUNCATE TABLE DIM_Date

DECLARE @CurrentDate DATE = '2020-01-01'

DECLARE @EndDate DATE = '2025-12-31'

WHILE @CurrentDate < @EndDate

BEGIN

   INSERT INTO [dbo].[Dim_Date] (

      [DateKey],

      [Date],

      [Day],

      [DaySuffix],

      [Weekday],

      [WeekDayName],

      [WeekDayName_Short],

      [WeekDayName_FirstLetter],

      [DOWInMonth],

      [DayOfYear],

      [WeekOfMonth],

      [WeekOfYear],

      [Month],

      [MonthName],

      [MonthName_Short],

      [MonthName_FirstLetter],

      [Quarter],

      [QuarterName],

      [Year],

      [MMYYYY],

      [MonthYear],

      [IsWeekend],

      [IsHoliday]

      )

   SELECT DateKey = YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate),

      DATE = @CurrentDate,

      Day = DAY(@CurrentDate),

      [DaySuffix] = CASE 

         WHEN DAY(@CurrentDate) = 1

            OR DAY(@CurrentDate) = 21

            OR DAY(@CurrentDate) = 31

            THEN 'st'

         WHEN DAY(@CurrentDate) = 2

            OR DAY(@CurrentDate) = 22

            THEN 'nd'

         WHEN DAY(@CurrentDate) = 3

            OR DAY(@CurrentDate) = 23

            THEN 'rd'

         ELSE 'th'

         END,

      WEEKDAY = DATEPART(dw, @CurrentDate),

      WeekDayName = DATENAME(dw, @CurrentDate),

      WeekDayName_Short = UPPER(LEFT(DATENAME(dw, @CurrentDate), 3)),

      WeekDayName_FirstLetter = LEFT(DATENAME(dw, @CurrentDate), 1),

      [DOWInMonth] = DAY(@CurrentDate),

      [DayOfYear] = DATENAME(dy, @CurrentDate),

      [WeekOfMonth] = DATEPART(WEEK, @CurrentDate) - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0)) + 1,

      [WeekOfYear] = DATEPART(wk, @CurrentDate),

      [Month] = MONTH(@CurrentDate),

      [MonthName] = DATENAME(mm, @CurrentDate),

      [MonthName_Short] = UPPER(LEFT(DATENAME(mm, @CurrentDate), 3)),

      [MonthName_FirstLetter] = LEFT(DATENAME(mm, @CurrentDate), 1),

      [Quarter] = DATEPART(q, @CurrentDate),

      [QuarterName] = CASE 

         WHEN DATENAME(qq, @CurrentDate) = 1

            THEN 'First'

         WHEN DATENAME(qq, @CurrentDate) = 2

            THEN 'second'

         WHEN DATENAME(qq, @CurrentDate) = 3

            THEN 'third'

         WHEN DATENAME(qq, @CurrentDate) = 4

            THEN 'fourth'

         END,

      [Year] = YEAR(@CurrentDate),

      [MMYYYY] = RIGHT('0' + CAST(MONTH(@CurrentDate) AS VARCHAR(2)), 2) + CAST(YEAR(@CurrentDate) AS VARCHAR(4)),

      [MonthYear] = CAST(YEAR(@CurrentDate) AS VARCHAR(4)) + UPPER(LEFT(DATENAME(mm, @CurrentDate), 3)),

      [IsWeekend] = CASE 

         WHEN DATENAME(dw, @CurrentDate) = 'Sunday'

            OR DATENAME(dw, @CurrentDate) = 'Saturday'

            THEN 1

         ELSE 0

         END,

      [IsHoliday] = 0

   SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)

END

 Sau khi tạo xong ta có  bảng sau: