为数据仓库创建和填充日期维度
4.88/5 (75投票s)
本文介绍如何为数据仓库创建和填充日期维度。
引言
本文特别有助于那些从事数据仓库和商业智能工作的人员。当需要从零开始建立新的数据仓库时，需要创建并填充具有各种日期值、日期键、星期类型、星期名称、月份、月份名称、季度等的日期维度。
日期维度在数据仓库设计中起着重要作用，它能够帮助您研究数据在一段时间内的行为和趋势。
您可以通过使用日期维度的各种字段对数据进行分组来研究您的数据。
例如
例如，我想按一年中的每个月分析总销售额，或者显示按一年中的每个季度计算的总销售额，或者显示一年或一个月中哪些天的总销售额更高。
在数据仓库中实现完整解决方案后，日期维度的关系可以帮助您轻松地对数据进行切片和切块分析。
因此，作为第一步，您需要设计您的日期维度、时间维度并用一系列值填充它们。
关于时间维度的设计，您可以参考我在 CodeProject 上发布的另一篇文章，“设计和填充具有 24 小时以上值的时间维度”。
此日期维度将根据世界各地使用的各种日期格式存储日期值，例如欧洲、英国、印度等使用的“dd-MM-yyyy”，而美国使用的格式是“MM-dd-yyyy”。
甚至有些国家将周一视为一周的第一天（如英国），而有些国家则将周日视为一周的第一天（如美国），因此我在日期维度中包含了这两种类型的值，以便根据需要使用。
美国和英国的全国性节假日列表也不同，脚本可以更新日期维度中的这些值，这将有助于研究特定全国性节假日的数据。
我期望读者具备一定的 T-SQL 中级知识来理解和使用下面的脚本，我还提供了实现特定任务的步骤，T-SQL 脚本还包含行内注释来解释各种步骤的目的。
本文包含两个脚本
脚本 1
此脚本将为您创建日期维度表并填充所有标准值。请参阅图 1。您需要按照脚本 1 部分下方提供的 4 个简单步骤进行操作。
图 1
脚本 2
此脚本可用于扩展您的日期维度，使其包含财政日历字段，如财政年度、财政月份、财政季度等，并填充这些字段的相应值。财政日历字段可用于按定义的财政年度研究数据。请参阅图 2 以获取更多详细信息。请按照脚本 2 部分下方提供的步骤来完成此任务。
图 2
脚本 1
按顺序执行 4 个简单步骤，以创建并填充日期维度值。
Using the Code
按照给定的步骤创建日期维度
- 打开 SQL Server Management Studio
- 连接数据库引擎
- 打开新的查询编辑器
- 将下面各步骤中提供的脚本逐个复制粘贴到新的查询编辑器窗口中
- 要运行给定的 SQL 脚本，请按 F5
步骤 1
有关表中每个字段的进一步解释以及它将保存什么类型的值，请参阅 T-SQL 脚本中的行内注释。
为日期维度创建表
BEGIN TRY
DROP TABLE [dbo].[DimDate]
END TRY
BEGIN CATCH
/*No Action*/
END CATCH
/**********************************************************************************/
CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
[FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
[DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
[DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
[DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
[DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
[DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),-- Week Number of Month
[WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
[WeekOfYear] VARCHAR(2),--Week Number of the Year
[Month] VARCHAR(2), --Number of the Month 1 to 12
[MonthName] VARCHAR(9),--January, February etc
[MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),--First,Second..
[Year] CHAR(4),-- Year value of Date stored in Row
[YearName] CHAR(7), --CY 2012,CY 2013
[MonthYear] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE,
[IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
[IsWeekday] BIT,-- 0=Week End ,1=Week Day
[HolidayUSA] VARCHAR(50),--Name of Holiday in US
[IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday
[HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
)
GO
对用于填充日期维度的函数进行简要介绍
| 函数 | 详细信息（例如，针对 2013 年 8 月 16 日） | |
| 1 | Select DATEPART(MM, Getdate()) as MonthNumber |
从当前日期返回月份的整数编号=8 |
| 2 | Select DATEPART(YY , Getdate()) as YearValue |
从当前日期返回年份的值=2013 |
| 3 | Select DATEPART(QQ , Getdate()) as QuarterValue |
返回当前日期的季度值=3 |
| 4 | Select DATEPART(DW, Getdate()) as DayOfWeekValue |
根据美国标准，返回当前日期所在星期的整数值=6（星期五） |
| 5 | Select CONVERT (char(8),Getdate(),112) |
返回当前日期的键值=20130816 |
| 6 | Select CONVERT (char(10),Getdate(),103) |
返回日期=16/08/2013，格式为“dd-MM-yyyy”，适用于英国、欧洲 |
| 7 | Select CONVERT (char(10),Getdate(),101) |
返回日期=08/16/2013，格式为“MM-dd-yyyy”，适用于美国 |
| 8 | Select DATEPART(DD , Getdate()) as DayOfMonthValue |
返回当前日期的月份中的日期值=16 |
| 9 | select DATENAME(DW, Getdate()) AS DayName |
返回当前日期的星期名称=星期五。 |
| 10 | select DATEPART(WW, Getdate()) AS WeekOfYear |
返回一年中的第 33 周 |
第二步
用值填充日期维度
您可以指定要在日期维度中填充的日期范围的开始日期和结束日期。
有关步骤的进一步解释，请参阅 T-SQL 脚本中的行内注释。
/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date
DECLARE @StartDate DATETIME = '01/01/2013' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2015' --End Value of Date Range
--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
--Extract and assign various parts of Values from Current Date to Variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE @CurrentDate < @EndDate
BEGIN
/*Begin day of week logic*/
/*Check for Change in Month of the Current date if Month changed then
Change variable value*/
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
/* Check for Change in Quarter of the Current date if Quarter changed then change
Variable value*/
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
/* Check for Change in Year of the Current date if Year changed then change
Variable value*/
IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)
/*End day of week logic*/
/* Populate Your Dimension Table with values*/
INSERT INTO [dbo].[DimDate]
SELECT
CONVERT (char(8),@CurrentDate,112) as DateKey,
@CurrentDate AS Date,
CONVERT (char(10),@CurrentDate,103) as FullDateUK,
CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
DATEPART(DD, @CurrentDate) AS DayOfMonth,
--Apply Suffix values like 1st, 2nd 3rd etc..
CASE
WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
END AS DaySuffix,
DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,
-- check for day of week as Per US and change it as per UK format
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END
AS DayOfWeekUK,
@DayOfWeekInMonth AS DayOfWeekInMonth,
@DayOfWeekInYear AS DayOfWeekInYear,
@DayOfQuarter AS DayOfQuarter,
DATEPART(DY, @CurrentDate) AS DayOfYear,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, _
DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, _
DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), _
@CurrentDate) / 7) + 1 AS WeekOfQuarter,
DATEPART(WW, @CurrentDate) AS WeekOfYear,
DATEPART(MM, @CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
DATEPART(QQ, @CurrentDate) AS Quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS QuarterName,
DATEPART(YEAR, @CurrentDate) AS Year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, _
DATEPART(YY, @CurrentDate)) AS MonthYear,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + _
CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
@CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
(DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, _
@CurrentDate)))) AS LastDayOfMonth,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, _
@CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, _
@CurrentDate))) AS LastDayOfYear,
NULL AS IsHolidayUSA,
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS IsWeekday,
NULL AS HolidayUSA, Null, Null
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
/********************************************************************************************/
Step 3.
Update Values of Holiday as per UK Government Declaration for National Holiday.
/*Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday*/
-- Good Friday April 18
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Good Friday'
WHERE [Month] = 4 AND [DayOfMonth] = 18
-- Easter Monday April 21
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Easter Monday'
WHERE [Month] = 4 AND [DayOfMonth] = 21
-- Early May Bank Holiday May 5
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Early May Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 5
-- Spring Bank Holiday May 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Spring Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 26
-- Summer Bank Holiday August 25
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Summer Bank Holiday'
WHERE [Month] = 8 AND [DayOfMonth] = 25
-- Boxing Day December 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Boxing Day'
WHERE [Month] = 12 AND [DayOfMonth] = 26
--CHRISTMAS
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
--New Years Day
UPDATE [dbo].[DimDate]
SET HolidayUK = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
--Update flag for UK Holidays 1= Holiday, 0=No Holiday
UPDATE [dbo].[DimDate]
SET IsHolidayUK = CASE WHEN HolidayUK IS NULL _
THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END
Step 4.
Update Values of Holiday as per USA Govt. Declaration for National Holiday.
/*Update HOLIDAY Field of USA In dimension*/
/*THANKSGIVING - Fourth THURSDAY in November*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Thanksgiving Day'
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Thursday'
AND DayOfWeekInMonth = 4
/*CHRISTMAS*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
/*4th of July*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Independance Day'
WHERE [Month] = 7 AND [DayOfMonth] = 4
/*New Years Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
/*Memorial Day - Last Monday in May*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Memorial Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MAX(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'May'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
/*Labor Day - First Monday in September*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Labor Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MIN(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'September'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
/*Valentine's Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Valentine''s Day'
WHERE
[Month] = 2
AND [DayOfMonth] = 14
/*Saint Patrick's Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Saint Patrick''s Day'
WHERE
[Month] = 3
AND [DayOfMonth] = 17
/*Martin Luthor King Day - Third Monday in January starting in 1983*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Martin Luthor King Jr Day'
WHERE
[Month] = 1
AND [DayOfWeekUSA] = 'Monday'
AND [Year] >= 1983
AND DayOfWeekInMonth = 3
/*President's Day - Third Monday in February*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'President''s Day'
WHERE
[Month] = 2
AND [DayOfWeekUSA] = 'Monday'
AND DayOfWeekInMonth = 3
/*Mother's Day - Second Sunday of May*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Mother''s Day'
WHERE
[Month] = 5
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 2
/*Father's Day - Third Sunday of June*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Father''s Day'
WHERE
[Month] = 6
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 3
/*Halloween 10/31*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Halloween'
WHERE
[Month] = 10
AND [DayOfMonth] = 31
/*Election Day - The first Tuesday after the first Monday in November*/
BEGIN
DECLARE @Holidays TABLE (ID INT IDENTITY(1,1), _
DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
INSERT INTO @Holidays(DateID, [Year],[Day])
SELECT
DateKey,
[Year],
[DayOfMonth]
FROM [dbo].[DimDate]
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Monday'
ORDER BY
YEAR,
DayOfMonth
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT
SELECT
@CURRENTYEAR = MIN([Year])
, @STARTYEAR = MIN([Year])
, @ENDYEAR = MAX([Year])
FROM @Holidays
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([Year])
FROM @Holidays
WHERE [Year] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM @Holidays
WHERE
[Year] = @CURRENTYEAR
AND [Week] IS NULL
UPDATE @Holidays
SET [Week] = @POS
WHERE
[Year] = @CURRENTYEAR
AND [Day] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Election Day'
FROM [dbo].[DimDate] DT
JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
WHERE
[Week] = 1
END
--set flag for USA holidays in Dimension
UPDATE [dbo].[DimDate]
SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
/*****************************************************************************************/
SELECT * FROM [dbo].[DimDate]
脚本 2
扩展日期维度，使其包含财政日历字段，如财政年度、财政月份、财政季度等。
Using the Code
请按照给定的步骤将与财政日历相关的新字段添加到日期维度中并用值填充它们。
- 打开 SQL Server Management Studio
- 连接数据库引擎
- 打开新的查询编辑器
- 将下面提供的脚本按顺序逐个复制粘贴
- 要运行给定的 SQL 脚本，请按 F5
步骤 1
向日期维度添加与财政日历相关的新字段
/*Add Fiscal Calendar columns into table DimDate*/
ALTER TABLE [dbo].[DimDate] ADD
[FiscalDayOfYear] VARCHAR(3),
[FiscalWeekOfYear] VARCHAR(3),
[FiscalMonth] VARCHAR(2),
[FiscalQuarter] CHAR(1),
[FiscalQuarterName] VARCHAR(9),
[FiscalYear] CHAR(4),
[FiscalYearName] CHAR(7),
[FiscalMonthYear] CHAR(10),
[FiscalMMYYYY] CHAR(6),
[FiscalFirstDayOfMonth] DATE,
[FiscalLastDayOfMonth] DATE,
[FiscalFirstDayOfQuarter] DATE,
[FiscalLastDayOfQuarter] DATE,
[FiscalFirstDayOfYear] DATE,
[FiscalLastDayOfYear] DATE
GO
第二步
在日期维度表中填充财政日历字段
/***************************************************************************
The following section needs to be populated for defining the fiscal calendar
***************************************************************************/
DECLARE
@dtFiscalYearStart SMALLDATETIME = 'January 01, 1995',
@FiscalYear INT = 1995,
@LastYear INT = 2025,
@FirstLeapYearInPeriod INT = 1996
/*****************************************************************************************/
DECLARE
@iTemp INT,
@LeapWeek INT,
@CurrentDate DATETIME,
@FiscalDayOfYear INT,
@FiscalWeekOfYear INT,
@FiscalMonth INT,
@FiscalQuarter INT,
@FiscalQuarterName VARCHAR(10),
@FiscalYearName VARCHAR(7),
@LeapYear INT,
@FiscalFirstDayOfYear DATE,
@FiscalFirstDayOfQuarter DATE,
@FiscalFirstDayOfMonth DATE,
@FiscalLastDayOfYear DATE,
@FiscalLastDayOfQuarter DATE,
@FiscalLastDayOfMonth DATE
/*Holds the years that have 455 in last quarter*/
DECLARE @LeapTable TABLE (leapyear INT)
/*TABLE to contain the fiscal year calendar*/
DECLARE @tb TABLE(
PeriodDate DATETIME,
[FiscalDayOfYear] VARCHAR(3),
[FiscalWeekOfYear] VARCHAR(3),
[FiscalMonth] VARCHAR(2),
[FiscalQuarter] VARCHAR(1),
[FiscalQuarterName] VARCHAR(9),
[FiscalYear] VARCHAR(4),
[FiscalYearName] VARCHAR(7),
[FiscalMonthYear] VARCHAR(10),
[FiscalMMYYYY] VARCHAR(6),
[FiscalFirstDayOfMonth] DATE,
[FiscalLastDayOfMonth] DATE,
[FiscalFirstDayOfQuarter] DATE,
[FiscalLastDayOfQuarter] DATE,
[FiscalFirstDayOfYear] DATE,
[FiscalLastDayOfYear] DATE)
/*Populate the table with all leap years*/
SET @LeapYear = @FirstLeapYearInPeriod
WHILE (@LeapYear < @LastYear)
BEGIN
INSERT INTO @leapTable VALUES (@LeapYear)
SET @LeapYear = @LeapYear + 5
END
/*Initiate parameters before loop*/
SET @CurrentDate = @dtFiscalYearStart
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalMonth = 1
SET @FiscalQuarter = 1
SET @FiscalWeekOfYear = 1
IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
BEGIN
SET @LeapWeek = 1
END
ELSE
BEGIN
SET @LeapWeek = 0
END
/*******************************************************************************************/
/* Loop on days in interval*/
WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
/*SET fiscal Month*/
SELECT @FiscalMonth = CASE
/*Use this section for a 4-5-4 calendar.
Every leap year the result will be a 4-5-5*/
WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11
/*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12
/*4 weeks (5 weeks on leap year)*/
/*Use this section for a 4-4-5 calendar.
Every leap year the result will be a 4-5-5*/
/*
WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 44 AND _
(47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
WHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12 /*5 weeks*/
*/
END
/*SET Fiscal Quarter*/
SELECT @FiscalQuarter = CASE
WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
END
SELECT @FiscalQuarterName = CASE
WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 'First'
WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 'Second'
WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 'Third'
WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 'Fourth'
END
/*Set Fiscal Year Name*/
SELECT @FiscalYearName = 'FY ' + CONVERT(VARCHAR, @FiscalYear)
INSERT INTO @tb (PeriodDate, FiscalDayOfYear, FiscalWeekOfYear, _
fiscalMonth, FiscalQuarter, FiscalQuarterName, FiscalYear, FiscalYearName) VALUES
(@CurrentDate, @FiscalDayOfYear, @FiscalWeekOfYear, @FiscalMonth, _
@FiscalQuarter, @FiscalQuarterName, @FiscalYear, @FiscalYearName)
/*SET next day*/
SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
SET @FiscalDayOfYear = @FiscalDayOfYear + 1
SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1
IF (@FiscalWeekOfYear > (52+@LeapWeek))
BEGIN
/*Reset a new year*/
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalYear = @FiscalYear + 1
IF ( EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
BEGIN
SET @LeapWeek = 1
END
ELSE
BEGIN
SET @LeapWeek = 0
END
END
END
/********************************************************************************************/
/*Set first and last days of the fiscal months*/
UPDATE @tb
SET
FiscalFirstDayOfMonth = minmax.StartDate,
FiscalLastDayOfMonth = minmax.EndDate
FROM
@tb t,
(
SELECT FiscalMonth, FiscalQuarter, FiscalYear, _
MIN(PeriodDate) AS StartDate, MAX(PeriodDate) AS EndDate
FROM @tb
GROUP BY FiscalMonth, FiscalQuarter, FiscalYear
) minmax
WHERE
t.FiscalMonth = minmax.FiscalMonth AND
t.FiscalQuarter = minmax.FiscalQuarter AND
t.FiscalYear = minmax.FiscalYear
/*Set first and last days of the fiscal quarters*/
UPDATE @tb
SET
FiscalFirstDayOfQuarter = minmax.StartDate,
FiscalLastDayOfQuarter = minmax.EndDate
FROM
@tb t,
(
SELECT FiscalQuarter, FiscalYear, min(PeriodDate) _
as StartDate, max(PeriodDate) as EndDate
FROM @tb
GROUP BY FiscalQuarter, FiscalYear
) minmax
WHERE
t.FiscalQuarter = minmax.FiscalQuarter AND
t.FiscalYear = minmax.FiscalYear
/*Set first and last days of the fiscal years*/
UPDATE @tb
SET
FiscalFirstDayOfYear = minmax.StartDate,
FiscalLastDayOfYear = minmax.EndDate
FROM
@tb t,
(
SELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
FROM @tb
GROUP BY FiscalYear
) minmax
WHERE
t.FiscalYear = minmax.FiscalYear
/*Set FiscalYearMonth*/
UPDATE @tb
SET
FiscalMonthYear =
CASE FiscalMonth
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END + '-' + CONVERT(VARCHAR, FiscalYear)
/*Set FiscalMMYYYY*/
UPDATE @tb
SET
FiscalMMYYYY = RIGHT('0' + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)
/********************************************************************************************/
UPDATE [dbo].[DimDate]
SET
FiscalDayOfYear = a.FiscalDayOfYear
, FiscalWeekOfYear = a.FiscalWeekOfYear
, FiscalMonth = a.FiscalMonth
, FiscalQuarter = a.FiscalQuarter
, FiscalQuarterName = a.FiscalQuarterName
, FiscalYear = a.FiscalYear
, FiscalYearName = a.FiscalYearName
, FiscalMonthYear = a.FiscalMonthYear
, FiscalMMYYYY = a.FiscalMMYYYY
, FiscalFirstDayOfMonth = a.FiscalFirstDayOfMonth
, FiscalLastDayOfMonth = a.FiscalLastDayOfMonth
, FiscalFirstDayOfQuarter = a.FiscalFirstDayOfQuarter
, FiscalLastDayOfQuarter = a.FiscalLastDayOfQuarter
, FiscalFirstDayOfYear = a.FiscalFirstDayOfYear
, FiscalLastDayOfYear = a.FiscalLastDayOfYear
FROM @tb a
INNER JOIN [dbo].[DimDate] b ON a.PeriodDate = b.[Date]
/********************************************************************************************/
SELECT * FROM [dbo].[DimDate]
享受 T-SQL 化的乐趣。