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.
/* 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