In this article, you will find the SQL with easy modification to 'start date' and 'end date' only, that will provide accurate generation of values, in a straightforward manner, for your DimDate implementation.
Introduction
Over time, I have had the experience of working with several implementations of DimDate. Some exhibit subtle errors, especially in quarter related data and especially more so, in fiscal year related values. The SQL provided in this article, with easy modification to 'start date' and 'end date' only, will provide accurate generation of values, in a straightforward manner, for your DimDate implementation. The related file, 'DimDate.zip' contains all of the SQL presented in this article. SQL is provided for creating tables and view, populating tables with values, checking the results, and comparing to other implementations.
Background
To facilitate straightforward generation of values for DimDate with accurate data, I adhere to the following two overarching design principles.
DimDate is actually a view (vwDimDate) that joins appropriate tables such as CalendarYear, FiscalYear, HolidayCalendar, country or cultural specific calendar, personal tickler calendar, etc. This allows us to focus on generating domain specific tables without the need for complex logic to place 'many' values into a single table appropriately. Then, views can be created to present the needed data.- Tables that exhibit physical calendar tendencies (years, quarters, months, weeks, day of week) can be accurately generated with nested
while loops and need not be dependent on complicated if/else logic, internal support tables, tables representing varying configurations for quarters, etc. Consider that within any year there are quarters, within a quarter there are months, within a month, there are weeks, and within a week, there are days of the week. Using nested 'While' statements is an excellent way to generate values for such a nested structure.
The following points will be of value to understand as you utilize the associated SQL (SQL Server T-SQL):
- yyyymmdd – represents a date as year, month (01..12), day (01..31) (This also happens to be a useful format as a prefix for file names to facilitate sorting and subsequent productivity in focusing on file names.)
- mm/dd/yyyy – represents a date as month (01..12), day (01..31), year
- leap year (i.e., February has 29 days rather than 28) – When the year = yy00 then yyyy must be evenly divisible by 400 to be a leap year. Otherwise, yyyy must be evenly divisible by 4 to be a leap year. If not evenly divided (i.e., there is a remainder) by either 400 or 4 as described, then the year is NOT a leap year and month February has 28 days for the subject year.
- century – Years 1..100 (inclusive of end points) is commonly considered to be the 1st century. Years 101..200, the 2nd century and so on. Thus, years 1901..2000 comprise the 20th century, 2001..2100 comprise the 21st century.
- Fiscal year – A 12 month accounting period not normally coinciding with a calendar year (1/1/yyyy..12/31/yyyy). For example, fiscal year 2017, MAY refer to 5/1/2016..4/30/2017. Typically, when someone states a particular year, they are referring to the calendar year in which the fiscal year ends. However, NEVER assume what dates comprise a particular fiscal year. Ask, ‘What specific dates comprise the subject fiscal year?’ This is very important.
- Months – January, February, … November, December
- Days of Week – Sunday, Monday, … Friday, Saturday. Sunday is considered to be the first day of each week.
- The attributes created are minimal and should be modified (deletions, additions, changes) for your personal or organizational needs.
Generating Values for a Calendar
Consider that a calendar (CalendarYear or FiscalYear) is comprised of years. Within each year, whether calendar or fiscal, are quarters (3 consecutive months), within each quarter - months, within each month - weeks (rows), and within each week - day of week (columns). Physically, each year can be visualized as 12 months where each month is 6 weeks. And each week is 7 days. This is a physical 'place holder' description. It does not state that each of the 6 week rows and day of the week in each week has a value. Actual values (day of the month) within weeks within months and months within quarter/year are ‘ragged’. That is, the 1st of each month does not always begin on the same day each week, and months do not have the same number of days. Using nested whiles is an excellent method for generating the appropriate rows of data for our calendars. The logic just needs to properly account for any ‘raggedness’.
Data Retention Policies
Understandably, DimDate continues to grow year after year or more likely, in chunks of years. What many of us tend not to deal with, is deleting ‘stale', no longer needed rows. Whenever we deal with that, we will need to adhere to our organization’s ‘data retention’ policies. And importantly, we also need to determine what ‘downstream’ reports, processes, etc. may fail or produce inaccurate results upon removing 'no longer needed' rows.
Using the Code
First, let's create CalendarYear and FiscalYear tables. And then create view - vwDimDate. The CalendarYear will be considered the principal table upon which other tables (e. g., FiscalYear) will be joined. And, as long as attribute names (column names) within the joined tables are unique, the view can be as simple as shown.
BEGIN TRY
DROP TABLE CalendarYear
END TRY
BEGIN CATCH
print 'Attempting to DROP TABLE ''CalendarYear'' - NOT FOUND.'
print 'continuing... to create table ''CalendarYear''.'
END CATCH
CREATE TABLE CalendarYear
( DateKey int primary key
,[Date] date
,[Date_mm/dd/yyyy] varchar(10)
,Date_ddMMMyyyy CHAR(09)
,Century int
,[Year] int
,IsLeapYear bit
,QuarterOfYear int
,QuarterOfYearName varchar(06)
,FirstOfQuarter date
,LastOfQuarter date
,MonthOfQuarter int
,WeekOfQuarter int
,DayOfQuarter int
,MonthOfYear int
,MonthOfYearNameLong varchar(09)
,MonthOfYearNameShort char(03)
,WeekOfMonth int
,[DayOfMonth] int
,DayOfMonthOrdinal varchar(04)
,WeekOfYear int
,[DayOfYear] int
,IsWeekDay bit
,[DayOfWeek] int
,DayOfWeekNameLong VARCHAR(09)
,DayOfWeekNameShort char(03)
)
GO
BEGIN TRY
DROP TABLE FiscalYear
END TRY
BEGIN CATCH
print 'Attempting to DROP TABLE ''FiscalYear'' - NOT FOUND.'
print 'continuing... to create table ''FiscalYear''.'
END CATCH
CREATE TABLE FiscalYear
( FYDateKey int primary key
,FYDate date
,FYYear int
,FYQuarterOfYear int
,FYQuarterOfYearName varchar(06)
,FYFirstOfQuarter date
,FYLastOfQuarter date
,FYMonthOfQuarter int
,FYWeekOfQuarter int
,FYDayOfQuarter int
,FYMonthOfYear int
,FYWeekOfYear int
,FYDayOfYear int
)
go
BEGIN TRY
DROP VIEW vwDimDate
END TRY
BEGIN CATCH
print 'Attempting to DROP VIEW ''vwDimDate'' - NOT FOUND.'
print 'continuing... to create view ''vwDimDate''.'
END CATCH
go
create view vwDimDate
as
select CalendarYear.*, FiscalYear.*
from CalendarYear
left join FiscalYear
on CalendarYear.DateKey = FiscalYear.FYDateKey
go
Using the Code
Second, let's populate the CalendarYear table. Carefully examine any statements with '<-- Modify!' and modify values appropriate for your implementation. @StartDate is the variable you use to specify January 1 of the first calendar year you want to generate values for. @EndDate is the variable you use to specify December 31 of the final calendar year you want to generate values for. The provided SQL generates values for calendar years 2017..2021.
set nocount on
set datefirst 7
set noexec off
declare @StartDate date = '01/01/2017'
declare @EndDate date = '12/31/2021'
if @StartDate > @EndDate
begin
print 'StartDate ' +
convert(char(08),@StartDate,112) +
' is greater than EndDate ' +
convert(char(08),@EndDate,112) +
'.'
set noexec on
end
if datepart(month,@StartDate) <> 1 or datepart(day,@StartDate) <> 1
begin
print 'Month ' +
cast(datepart(month,@StartDate) as varchar) +
' or day ' +
cast(datepart(day,@StartDate) as varchar) +
' in ' +
convert(char(08),@StartDate,112) +
' is not ''1''. Both must be ''1'' for calendar year.'
set noexec on
end
if ((datediff(month,@StartDate,@EndDate)+1) % 12) <> 0
begin
print convert(char(08),@StartDate,112) +
'...' +
convert(char(08),@EndDate,112) +
' covers ' +
cast(datediff(month,@StartDate,@EndDate) + 1 as varchar) +
' months. Not a multiple of 12.'
set noexec on
end
declare @CurrentDate date = @StartDate
declare @RunningYear int = datepart(year,@StartDate)
declare @EndingYear int = datepart(year,@EndDate)
declare @QuarterOfYear int
declare @MonthOfYear int
declare @MonthOfQuarter int
declare @WeekOfYear int
declare @WeekOfQuarter int
declare @WeekOfMonth int
declare @DayOfYear int
declare @DayOfQuarter int
declare @DayOfMonth int
declare @FirstOfQuarter date
while @RunningYear <= @EndingYear
begin
set @QuarterOfYear = 1
set @MonthOfYear = 1
set @WeekOfYear = 1
set @DayOfYear = 1
set @FirstOfQuarter = @CurrentDate
while @QuarterOfYear <= 4
begin
set @MonthOfQuarter = 1
set @WeekOfQuarter = 1
set @DayOfQuarter = 1
while @MonthOfQuarter <= 3
begin
set @WeekOfMonth = 1
set @DayOfMonth = 1
while @WeekOfMonth <= 6
begin
INSERT INTO CalendarYear
SELECT
CONVERT (char(8),@CurrentDate,112)
,@CurrentDate
,cast(datepart(mm,@CurrentDAte) as varchar) +
'/' +
cast(datepart(dd,@CurrentDate) as varchar) +
'/' +
cast(datepart(yy,@CurrentDate) as varchar)
,cast(datepart(dd,@CurrentDate) as varchar) +
upper(left(datename(mm,@CurrentDate),3)) +
cast(datepart(yy,@CurrentDate) as varchar)
,case
when datepart(year,@CurrentDate) % 100 = 0
then datepart(year,@CurrentDate) / 100
else (datepart(year,@CurrentDate) / 100) + 1
end
,datepart(year,@CurrentDate)
,case
when(DATEPART(year,@CurrentDate) % 4 = 0
and
DATEPART(year,@CurrentDAte) % 100 <> 0)
or
DATEPART(year,@CurrentDAte) % 400 = 0
then 1 else 0
end
,@QuarterOfYear
,case @QuarterOfYear
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END
,@FirstOfQuarter
,dateadd(day,-1,dateadd(month,3,@FirstOfQuarter))
,@MonthOfQuarter
,@WeekOfQuarter
,@DayOfQuarter
,@MonthOfyear
,DATENAME(MM, @CurrentDate)
,LEFT(DATENAME(MM, @CurrentDate), 3)
,@WeekOfMonth
,@DayOfMonth
,CASE
WHEN DATEPART(day,@CurrentDate) IN (11,12,13)
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(day,@CurrentDate),1) = 1
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(day,@CurrentDate),1) = 2
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(day,@CurrentDate),1) = 3
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'th'
END
,@WeekOfYear
,@DayOfYear
,CASE DATEPART(weekday, @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
,DATEPART(weekday,@CurrentDate)
,DATENAME(weekday, @CurrentDate)
,LEFT(DATENAME(weekday, @CurrentDate), 3)
set @DayOfYear = @DayOfYear + 1
set @DayOfQuarter = @DayOfQuarter + 1
set @DayOfMonth = @DayOfMonth + 1
if DATEPART(weekday,@CurrentDate) = 7
begin
set @WeekOfMonth = @WeekOfMonth + 1
set @WeekOfQuarter = @WeekOfQuarter + 1
set @WeekOfYear = @WeekOfYear + 1
end
if datepart(month,@CurrentDate) <> _
datepart(month,dateadd(dd,1,@CurrentDate))
begin
set @MonthOfYear = @MonthOfYear + 1
set @MonthOfQuarter = @MonthOfQuarter + 1
set @WeekOfMonth = 32767
end
set @CurrentDate = dateadd(DD, 1, @CurrentDate)
end
set @WeekOfMonth = @WeekOfMonth + 1
end
set @QuarterOfYear = @QuarterOfYear + 1
set @FirstOfQuarter = dateadd(month,3,@FirstOfQuarter)
end
set @RunningYear = @RunningYear + 1
end
go
Using the Code
Third, let's populate the FiscalYear table. Carefully examine any statements with '<-- Modify!' and modify values appropriate for your implementation. @StartDate is the variable you use to specify day 1 of month 1 of the first fiscal year you want to generate values for. @EndDate is the variable you use to specify the last day of the last month of the final fiscal year you want to generate values for. The provided SQL generates values for fiscal years 2017..2022 where each fiscal year begins May 1 and ends April 30. @StartFiscalYear and @EndFiscalYear are the variables where you specify the first and final fiscal year, respectively. Thus, the provided SQL generates values covering 20160501..20220430.
Now you and others are ready to benefit from using vwDimDate.
set nocount on
set datefirst 7
set noexec off
declare @StartDate date = '05/01/2016'
declare @EndDate date = '04/30/2022'
declare @StartFiscalYear int = 2017
declare @EndFiscalYear int = 2022
if @StartDate > @EndDate
begin
print 'StartDate ' +
convert(char(08),@StartDate,112) +
' is greater than EndDate ' +
convert(char(08),@EndDate,112) +
'.'
set noexec on
end
if datepart(day,@StartDate) <> 1
begin
print 'Day ' +
cast(datepart(day,@StartDate) as varchar) +
' in ' +
convert(char(08),@StartDate,112) +
' is not ''1''. Must be ''1'' for fiscal year.'
set noexec on
end
if ((datediff(month,@StartDate,@EndDate)+1) % 12) <> 0
begin
print convert(char(08),@StartDate,112) +
'...' +
convert(char(08),@EndDate,112) +
' covers ' +
cast(datediff(month,@StartDate,@EndDate) + 1 as varchar) +
' months. Not a multiple of 12.'
set noexec on
end
declare @CurrentDate date = @StartDate
declare @RunningYear int = @StartFiscalYear
declare @EndingYear int = @EndFiscalYear
declare @QuarterOfYear int
declare @MonthOfYear int
declare @MonthOfQuarter int
declare @WeekOfYear int
declare @WeekOfQuarter int
declare @WeekOfMonth int
declare @DayOfYear int
declare @DayOfQuarter int
declare @DayOfMonth int
declare @FirstOfQuarter date
while @RunningYear <= @EndingYear
begin
set @QuarterOfYear = 1
set @MonthOfYear = 1
set @WeekOfYear = 1
set @DayOfYear = 1
set @FirstOfQuarter = @CurrentDate
while @QuarterOfYear <= 4
begin
set @MonthOfQuarter = 1
set @WeekOfQuarter = 1
set @DayOfQuarter = 1
while @MonthOfQuarter <= 3
begin
set @WeekOfMonth = 1
set @DayOfMonth = 1
while @WeekOfMonth <= 6
begin
INSERT INTO FiscalYear
SELECT
CONVERT (char(8),@CurrentDate,112)
,@CurrentDate
,@RunningYear
,@QuarterOfYear
,case @QuarterOfYear
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END
,@FirstOfQuarter
,dateadd(day,-1,dateadd(month,3,@FirstOfQuarter))
,@MonthOfQuarter
,@WeekOfQuarter
,@DayOfQuarter
,@MonthOfyear
,@WeekOfYear
,@DayOfYear
set @DayOfYear = @DayOfYear + 1
set @DayOfQuarter = @DayOfQuarter + 1
set @DayOfMonth = @DayOfMonth + 1
if DATEPART(weekday,@CurrentDate) = 7
begin
set @WeekOfMonth = @WeekOfMonth + 1
set @WeekOfQuarter = @WeekOfQuarter + 1
set @WeekOfYear = @WeekOfYear + 1
end
if datepart(month,@CurrentDate) <> _
datepart(month,dateadd(dd,1,@CurrentDate))
begin
set @MonthOfYear = @MonthOfYear + 1
set @MonthOfQuarter = @MonthOfQuarter + 1
set @WeekOfMonth = 32767
end
set @CurrentDate = dateadd(DD, 1, @CurrentDate)
end
set @WeekOfMonth = @WeekOfMonth + 1
end
set @QuarterOfYear = @QuarterOfYear + 1
set @FirstOfQuarter = dateadd(month,3,@FirstOfQuarter)
end
set @RunningYear = @RunningYear + 1
end
go
Using the Code
Let's check the result of populating our tables.
select 'Data for today requested at ' +
convert(char(05),getdate(),114) +
'h -->' RequestTStamp, *
from vwDimDate
where DateKey
=
convert(char(08),getdate(),112)
select * from vwDimDate order by DateKey
Using the Code
Compare this (vwDimDate) implementation to another. The following SQL is a framework for comparing this vwDimdate implementation to other 'DimDate' implementations.
select This.DateKey
,This.FYYear
,Other.DateKey
,Other.FiscalYear
from vwDimDate This
join
DimDate Other
on This.DateKey = Other.DateKey
where This.FYYear <> Other.FiscalYear
Points of Interest
- Use of views facilitate focused development and accurate population of domain specific tables.
- Nested '
Whiles' aid in generating accurate values especially for nested data structures. - 'Accuracy first, neatness next, speed will come eventually.'
History
- 11th December, 2017: Initial version
