Introduction
This article will especially help those people who work in Data warehouse and Business Intelligence. Whenever as a starting point, they need to set New Data warehouse, during this time they need to create and fill their Date Dimension with various values of Date, Date Keys, Day Type, Day Name Of Week, Month, Month Name, Quarter, etc.
Date dimension plays an important role in your data warehouse designing, it provides the ability to study behavior and trend of your data over a period of time.
You can study your data by grouping them using various fields of date dimension.
For example:
If I want to analyze my data of total sales by each month of the year, or show total sales by each quarter of the year, or show me on which days total sales takes place more in the entire year or month.
After implementing the complete solution in data warehouse, the relationship of date dimension gives you all this facility to do slice and dice of your data.
So as an initial step, you need to design your date dimension, time dimension and populate them with range of values.
For designing of time dimension, you can refer to my other tip posted on CodeProject, “Design and Populate Time Dimension with 24 Hour plus Values”.
This date dimension will have values of date stored as per various date formats used across the world, like “dd-MM-yyyy” is used in Europe, UK, India, etc. while “MM-dd-yyyy” format is used in US.
Even some countries refer to Monday as 1st day of the week like UK, and some of them refer to Sunday as the 1st day of the week like US, so I have placed both types of values in this date dimension so that it can be utilized as per the need.
National holiday list is also different in both the countries US and UK, script is there to update date dimension with these values, it will help to study data on a particular national holiday.
I expect some intermediate knowledge of T-SQL from the reader to understand and to use the script given below, even I have placed steps to follow to achieve a particular task , T-SQL script also contains in line comments to explain the purpose of various steps.
This article contains two scripts:
Script 1
This script will create date dimension table for you and populate it with all standard values. Please refer to figure 1. You need to follow 4 easy steps given under section of the Script 1.
Figure 1
Script 2
This script can be used to extend your date dimension with Fiscal Calendar fields like Fiscal Year, Fiscal month, fiscal Quarter, etc and populate these fields with appropriate values, Fiscal Calendar fields can be used to study data as per Financial Year Defined. Please refer to figure 2 for further details. Follow the steps given under section of Script 2 to accomplish this task.
Figure 2
SCRIPT 1
Follow the 4 easy steps in sequence to create and populate date dimension with values.
Using the Code
Follow the given steps to create date dimension:
- Open SQL Server Management Studio
- Connect Database Engine
- Open New query editor
- Copy paste scripts given below in various steps in new query editor window one by one
- To run the given SQL script, press F5
Step 1
Please refer to the inline comments given with T-SQL Script for further explanation of each field in table and which type of values it will hold.
Create Table for Date Dimension
BEGIN TRY
DROP TABLE [dbo].[DimDate]
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDateUK] CHAR(10),
[FullDateUSA] CHAR(10),
[DayOfMonth] VARCHAR(2),
[DaySuffix] VARCHAR(4),
[DayName] VARCHAR(9),
[DayOfWeekUSA] CHAR(1),
[DayOfWeekUK] CHAR(1),
[DayOfWeekInMonth] VARCHAR(2),
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),
[WeekOfQuarter] VARCHAR(2),
[WeekOfYear] VARCHAR(2),
[Month] VARCHAR(2),
[MonthName] VARCHAR(9),
[MonthOfQuarter] VARCHAR(2),
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),
[Year] CHAR(4),
[YearName] CHAR(7),
[MonthYear] CHAR(10),
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE,
[IsHolidayUSA] BIT,
[IsWeekday] BIT,
[HolidayUSA] VARCHAR(50),
[IsHolidayUK] BIT Null,
[HolidayUK] VARCHAR(50) Null
)
GO
Brief introduction to functions used in script to populate Date Dimension
| Function | Detail (e.g. for 16-Aug-2013) |
| 1 | Select DATEPART(MM, Getdate()) as MonthNumber | Return Integer Number=8 of Month from Current Date |
| 2 | Select DATEPART(YY , Getdate()) as YearValue | Return Value of the Year=2013 from Current Date |
| 3 | Select DATEPART(QQ , Getdate()) as QuarterValue | Return Value of the Quarter=3 for Current Date |
| 4 | Select DATEPART(DW, Getdate()) as DayOfWeekValue | Return integer Value of day=6 (Friday) in Week for Current Date as per US standard |
| 5 | Select CONVERT (char(8),Getdate(),112) | Return Key=20130816 Value for current Date |
| 6 | Select CONVERT (char(10),Getdate(),103) | Return date =16/08/2013 in “dd-MM-yyyy” format, UK, Europe |
| 7 | Select CONVERT (char(10),Getdate(),101) | Return date=08/16/2013 in “MM-dd-yyyy” format, US |
| 8 | Select DATEPART(DD , Getdate()) as DayOfMonthValue | Return integer Day=16 Value for Current Date |
| 9 | select DATENAME(DW, Getdate()) AS DayName | Return Name=Friday of the Day for Current Date. |
| 10 | select DATEPART(WW, Getdate()) AS WeekOfYear | Returns Value of Week in Year=33 |
Step 2
Populate Date dimension with values
You can specify start date and end date value of date range which you want to populate in your date dimension.
Please refer to the inline comments given with T-SQL script for further explanation of steps.
DECLARE @StartDate DATETIME = '01/01/2013'
DECLARE @EndDate DATETIME = '01/01/2015'
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
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)
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
WHILE @CurrentDate < @EndDate
BEGIN
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
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)
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,
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,
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 [dbo].[DimDate]
SET HolidayUK = 'Good Friday'
WHERE [Month] = 4 AND [DayOfMonth] = 18
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Easter Monday'
WHERE [Month] = 4 AND [DayOfMonth] = 21
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Early May Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 5
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Spring Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Summer Bank Holiday'
WHERE [Month] = 8 AND [DayOfMonth] = 25
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Boxing Day'
WHERE [Month] = 12 AND [DayOfMonth] = 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
UPDATE [dbo].[DimDate]
SET HolidayUK = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
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 [dbo].[DimDate]
SET HolidayUSA = 'Thanksgiving Day'
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Thursday'
AND DayOfWeekInMonth = 4
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Independance Day'
WHERE [Month] = 7 AND [DayOfMonth] = 4
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
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]
)
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]
)
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Valentine''s Day'
WHERE
[Month] = 2
AND [DayOfMonth] = 14
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Saint Patrick''s Day'
WHERE
[Month] = 3
AND [DayOfMonth] = 17
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Martin Luthor King Jr Day'
WHERE
[Month] = 1
AND [DayOfWeekUSA] = 'Monday'
AND [Year] >= 1983
AND DayOfWeekInMonth = 3
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'President''s Day'
WHERE
[Month] = 2
AND [DayOfWeekUSA] = 'Monday'
AND DayOfWeekInMonth = 3
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Mother''s Day'
WHERE
[Month] = 5
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 2
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Father''s Day'
WHERE
[Month] = 6
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 3
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Halloween'
WHERE
[Month] = 10
AND [DayOfMonth] = 31
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
UPDATE [dbo].[DimDate]
SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
SELECT * FROM [dbo].[DimDate]
SCRIPT 2
Extension of date dimension with fiscal calendar fields like Fiscal Year, Fiscal Month, and Fiscal Quarter, etc.
Using the Code
Follow the given steps to add new fields related to Fiscal calendar in date dimension and populate them with values.
- Open SQL Server Management Studio
- Connect Database Engine
- Open New query editor
- Copy paste scripts given below in sequence of one by one
- To run the given SQL Script, press F5
Step 1
Add new Fields in Date dimension related to Fiscal Calendar
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
Step 2
Populate Fiscal Calendar fields in Dim date table
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
DECLARE @LeapTable TABLE (leapyear INT)
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)
SET @LeapYear = @FirstLeapYearInPeriod
WHILE (@LeapYear < @LastYear)
BEGIN
INSERT INTO @leapTable VALUES (@LeapYear)
SET @LeapYear = @LeapYear + 5
END
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
WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
SELECT @FiscalMonth = CASE
WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1
WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2
WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4
WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5
WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7
WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8
WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10
WHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11
WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12
WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2
WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4
WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5
WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7
WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8
WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10
WHEN @FiscalWeekOfYear BETWEEN 44 AND _
(47+@leapWeek) THEN 11
WHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12
*/
END
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
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 @CurrentDate = DATEADD(dd, 1, @CurrentDate)
SET @FiscalDayOfYear = @FiscalDayOfYear + 1
SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1
IF (@FiscalWeekOfYear > (52+@LeapWeek))
BEGIN
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
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
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
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
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)
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]
Enjoy T-SQLization.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
it should be an integer or number if I am wrong please give reason