DimDate Revisited (The Power of a View and Nested Whiles)
4.75/5 (4 votes)
Straightforward generation of and ensuring accuracy for DimDate values
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.
DimDateis actually a view (vwDimDate) that joins appropriate tables such asCalendarYear,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
whileloops and need not be dependent on complicatedif/elselogic, 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.
-- Create CalendarYear
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 -- ccyy0m0d (ccyy, zero m or mm, zero d or dd)
,[Date] date
,[Date_mm/dd/yyyy] varchar(10) -- mm/dd/yyyy (leading zeros in mm, dd removed)
,Date_ddMMMyyyy CHAR(09) -- dMMMYYYY or ddMMMYYYY
,Century int -- 1901..2000 - 20th century,
-- 2001..2100 - 21st century, etc.
,[Year] int -- ccyy
,IsLeapYear bit -- 0 = not a leap year, 1 = is a leap year
,QuarterOfYear int -- cardinal number of quarter of year (1..4)
,QuarterOfYearName varchar(06) -- First, Second, Third, Fourth
-- (ordinal name for calendar quarter)
,FirstOfQuarter date -- first date in quarter of year
,LastOfQuarter date -- last date in quarter of year
,MonthOfQuarter int -- 1..3 (calendar quarter)
,WeekOfQuarter int -- 1..14 (calendar quarter)
,DayOfQuarter int -- 1..92 (calendar quarter)
,MonthOfYear int -- cardinal number of month of year as d or dd
,MonthOfYearNameLong varchar(09) -- January.February.March.April.May.June.
-- July.August.September.October.
-- November.December
,MonthOfYearNameShort char(03) -- Jan.Feb.Mar.Apr.May.Jun.Jul.Aug.
-- Sep.Oct.Nov.Dec
,WeekOfMonth int -- 1..6
,[DayOfMonth] int -- cardinal number of day of month
,DayOfMonthOrdinal varchar(04) -- 1st, 2nd, 3rd, 4th, etc.
-- (ordinal number of day of month)
,WeekOfYear int -- 1..53
,[DayOfYear] int -- cardinal number of day of year
,IsWeekDay bit -- 0 = Saturday or Sunday, 1 = Monday..Friday
,[DayOfWeek] int -- 1..7 1 = Sunday...7 = Saturday
,DayOfWeekNameLong VARCHAR(09) -- Sunday.Monday.Tuesday.Wednesday.
-- Thursday.Friday.Saturday
,DayOfWeekNameShort char(03) -- Sun.Mon.Tue.Wed.Thu.Fri.Sat
)
GO
-- CreateFiscalYear
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 -- ccyy0m0d (ccyy, zero m or mm, zero d or dd)
,FYDate date
,FYYear int -- fiscal year as ccyy
,FYQuarterOfYear int -- cardinal number of quarter of year (1..4)
,FYQuarterOfYearName varchar(06) -- First, Second, Third, Fourth
-- (ordinal name for fiscal quarter)
,FYFirstOfQuarter date -- first date in quarter of fiscal year
,FYLastOfQuarter date -- last date in quarter of fiscal year
,FYMonthOfQuarter int -- 1..3 (fiscal quarter)
,FYWeekOfQuarter int -- 1..14 (fiscal quarter)
,FYDayOfQuarter int -- 1..92 (fiscal quarter)
,FYMonthOfYear int -- cardinal number of month of
-- fiscal year as d or dd
,FYWeekOfYear int -- 1..53
,FYDayOfYear int -- cardinal number of day of fiscal year
)
go
-- Create vwDimDate
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.
-- Populate CalendarYear
set nocount on -- Do NOT show number of rows affected.
set datefirst 7 -- Specify Sunday as the first day of the week.
set noexec off -- Specify execution of compiled SQL.
-- mm/dd/yyyy
declare @StartDate date = '01/01/2017' -- Starting value of Date Range as
-- month, day, year <-- Modify!
declare @EndDate date = '12/31/2021' -- Ending Value of Date Range as
-- month, day, year <-- Modify!
-- Produce calendar years for: 2017, 2018, 2019, 2020, 2021 <-- Modify!
-- Nothing below here to modify.
-- Calendar years cover 1/1/yyyy..12/31/yyyy.
if @StartDate > @EndDate -- If true, ensures production of 0 rows.
begin
print 'StartDate ' +
convert(char(08),@StartDate,112) +
' is greater than EndDate ' +
convert(char(08),@EndDate,112) +
'.'
set noexec on -- End execution.
end
if datepart(month,@StartDate) <> 1 or datepart(day,@StartDate) <> 1 -- starting date
-- is not 1/1/yyyy?
begin -- yes, ensure
-- production of 0 rows
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 execution.
end
if ((datediff(month,@StartDate,@EndDate)+1) % 12) <> 0 -- Number of months
-- not a multiple of 12?
begin -- yes, ensure
-- production of 0 rows
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 execution.
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 -- Begin year loop.
set @QuarterOfYear = 1
set @MonthOfYear = 1
set @WeekOfYear = 1
set @DayOfYear = 1
set @FirstOfQuarter = @CurrentDate
while @QuarterOfYear <= 4
begin -- Begin quarter loop.
set @MonthOfQuarter = 1
set @WeekOfQuarter = 1
set @DayOfQuarter = 1
while @MonthOfQuarter <= 3
begin -- Begin month loop.
set @WeekOfMonth = 1
set @DayOfMonth = 1
while @WeekOfMonth <= 6
-- Since weeks in months are ragged this loop will never end on WeekOfMonth = 7.
-- Rather, an arbitrary large value (32767) will force loop end when month changes.
-- The minimum value 6 is coded above to ensure the correct number of loop iterations.
begin -- Begin week loop.
-- Begin insert row.
INSERT INTO CalendarYear
SELECT
CONVERT (char(8),@CurrentDate,112) -- DateKey
,@CurrentDate -- Date
,cast(datepart(mm,@CurrentDAte) as varchar) +
'/' +
cast(datepart(dd,@CurrentDate) as varchar) +
'/' +
cast(datepart(yy,@CurrentDate) as varchar) -- Date_mm/dd/yyyy
,cast(datepart(dd,@CurrentDate) as varchar) +
upper(left(datename(mm,@CurrentDate),3)) +
cast(datepart(yy,@CurrentDate) as varchar) -- Date_ddMMMyyyy
,case
when datepart(year,@CurrentDate) % 100 = 0
then datepart(year,@CurrentDate) / 100
else (datepart(year,@CurrentDate) / 100) + 1
end -- Century
,datepart(year,@CurrentDate) -- Year
,case -- 1900 is not a leap year, 2000 is a leap year
when(DATEPART(year,@CurrentDate) % 4 = 0
and
DATEPART(year,@CurrentDAte) % 100 <> 0)
or
DATEPART(year,@CurrentDAte) % 400 = 0
then 1 else 0
end -- IsLeapYear
,@QuarterOfYear -- QuarterOfYear -- either this or ...
-- ,DATEPART(quarter,@CurrentDate) -- QuarterOfYear -- ... this works
-- for calendar year
,case @QuarterOfYear
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END -- QuarterOfYearName
,@FirstOfQuarter -- FirstOfQuarter
,dateadd(day,-1,dateadd(month,3,@FirstOfQuarter)) -- LastOfQuarter
,@MonthOfQuarter -- MonthOfQuarter
,@WeekOfQuarter -- WeeekOfQuarter
,@DayOfQuarter -- DayOfquarter
,@MonthOfyear -- MonthOfyear -- either this or ...
-- ,datepart(mm,@CurrentDate) -- MonthOfYear -- ... this works for
-- calendar year
,DATENAME(MM, @CurrentDate) -- MonthOfYearNameLong
,LEFT(DATENAME(MM, @CurrentDate), 3)-- MonthOfYearNameShort
,@WeekOfMonth -- WeekOfMonth
,@DayOfMonth -- 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 -- DayOfMonthOrdinal
,@WeekOfYear -- WeekOfYear
,@DayOfYear -- DayOfYear
,CASE DATEPART(weekday, @CurrentDate) -- dependent on 'set datefirst 7'
-- to indicate week begins on Sunday
WHEN 1 THEN 0 -- Sunday
WHEN 2 THEN 1 -- Monday
WHEN 3 THEN 1 -- Tuesday
WHEN 4 THEN 1 -- Wednesday
WHEN 5 THEN 1 -- Thursday
WHEN 6 THEN 1 -- Friday
WHEN 7 THEN 0 -- Saturday
END -- IsWeekday
,DATEPART(weekday,@CurrentDate) -- DayOfWeek
,DATENAME(weekday, @CurrentDate)-- DayOfWeekNameLong
,LEFT(DATENAME(weekday, @CurrentDate), 3) -- DayOfWeekNameShort
-- End insert row.
set @DayOfYear = @DayOfYear + 1 -- increment for next
-- loop interation
set @DayOfQuarter = @DayOfQuarter + 1 -- ...
set @DayOfMonth = @DayOfMonth + 1 -- ...
if DATEPART(weekday,@CurrentDate) = 7 -- Did a Sat just get inserted?
begin -- yes, it's a new week for next row
set @WeekOfMonth = @WeekOfMonth + 1
set @WeekOfQuarter = @WeekOfQuarter + 1
set @WeekOfYear = @WeekOfYear + 1
end
-- Look ahead to see if next day is in next month.
if datepart(month,@CurrentDate) <> _
datepart(month,dateadd(dd,1,@CurrentDate)) -- Month change?
begin -- yes
set @MonthOfYear = @MonthOfYear + 1
set @MonthOfQuarter = @MonthOfQuarter + 1
set @WeekOfMonth = 32767 -- Force end of week loop.
end
set @CurrentDate = dateadd(DD, 1, @CurrentDate) -- Increment to next day.
end -- End week loop.
set @WeekOfMonth = @WeekOfMonth + 1 -- increment for next iteration
-- in month loop
end --End month loop.
set @QuarterOfYear = @QuarterOfYear + 1 -- increment for next iteration
-- in quarter loop
set @FirstOfQuarter = dateadd(month,3,@FirstOfQuarter) -- new FirstOfQuarter
end -- End quarter loop.
set @RunningYear = @RunningYear + 1 -- increment for next iteration
-- in year loop
end -- End year loop.
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.
-- Populate FiscalYear
set nocount on -- Do NOT show number of rows affected.
set datefirst 7 -- Specify Sunday as the first day of the week.
set noexec off -- Specify execution of compiled SQL.
-- mm/dd/yyyy
declare @StartDate date = '05/01/2016' -- Starting value of Date Range as month,
-- day, year <-- Modify!
declare @EndDate date = '04/30/2022' -- Ending Value of Date Range as month,
-- day, year <-- Modify!
declare @StartFiscalYear int = 2017 -- Starting value of fiscal year <-- Modify!
declare @EndFiscalYear int = 2022 -- Ending value of fiscal year <-- Modify!
-- Produce fiscal years for: 2017, 2018, 2019, 2020, 2021, 2022 <-- Modify!
-- Fiscal years cover 5/1/yyyy..4/30/yyyy+1. <-- Modify!
-- Nothing below here to modify.
if @StartDate > @EndDate -- If true, ensures production of 0 rows.
begin
print 'StartDate ' +
convert(char(08),@StartDate,112) +
' is greater than EndDate ' +
convert(char(08),@EndDate,112) +
'.'
set noexec on -- End execution.
end
if datepart(day,@StartDate) <> 1 -- starting date is not mm/1/yyyy?
begin -- yes, ensure production of 0 rows
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 execution.
end
if ((datediff(month,@StartDate,@EndDate)+1) % 12) <> 0 -- Number of months not a
-- multiple of 12?
begin -- yes, ensure production
-- of 0 rows
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 execution.
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 -- Begin year loop.
set @QuarterOfYear = 1
set @MonthOfYear = 1
set @WeekOfYear = 1
set @DayOfYear = 1
set @FirstOfQuarter = @CurrentDate
while @QuarterOfYear <= 4
begin -- Begin quarter loop.
set @MonthOfQuarter = 1
set @WeekOfQuarter = 1
set @DayOfQuarter = 1
while @MonthOfQuarter <= 3
begin -- Begin month loop.
set @WeekOfMonth = 1
set @DayOfMonth = 1
while @WeekOfMonth <= 6
-- Since weeks in months are ragged this loop will never end on WeekOfMonth = 7.
-- Rather, an arbitrary large value (32767) will force loop end when month changes.
-- The minimum value 6 is coded above to ensure the correct number of loop iterations.
begin -- Begin week loop.
-- Begin insert row.
INSERT INTO FiscalYear
SELECT
CONVERT (char(8),@CurrentDate,112) -- FYDateKey
,@CurrentDate -- FYDate
,@RunningYear -- FYYear
,@QuarterOfYear -- FYQuarterOfYear
,case @QuarterOfYear
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END -- FYQuarterOfYearName
,@FirstOfQuarter -- FYFirstOfQuarter
,dateadd(day,-1,dateadd(month,3,@FirstOfQuarter)) -- FYLastOfQuarter
,@MonthOfQuarter -- FYMonthOfQuarter
,@WeekOfQuarter -- FYWeeekOfQuarter
,@DayOfQuarter -- FYDayOfquarter
,@MonthOfyear -- FYMonthOfyear
,@WeekOfYear -- FYWeekOfYear
,@DayOfYear -- FYDayOfYear
-- End insert row.
set @DayOfYear = @DayOfYear + 1 -- increment for
-- next loop interation
set @DayOfQuarter = @DayOfQuarter + 1 -- ...
set @DayOfMonth = @DayOfMonth + 1 -- ...
if DATEPART(weekday,@CurrentDate) = 7 -- Did a Sat just get inserted?
begin -- yes, it's a new week for next row
set @WeekOfMonth = @WeekOfMonth + 1
set @WeekOfQuarter = @WeekOfQuarter + 1
set @WeekOfYear = @WeekOfYear + 1
end
-- Look ahead to see if next day is in next month.
if datepart(month,@CurrentDate) <> _
datepart(month,dateadd(dd,1,@CurrentDate)) -- Month change?
begin
set @MonthOfYear = @MonthOfYear + 1 -- yes
set @MonthOfQuarter = @MonthOfQuarter + 1
set @WeekOfMonth = 32767 -- Force end of week loop.
end
set @CurrentDate = dateadd(DD, 1, @CurrentDate) -- Increment to next day.
end -- End week loop.
set @WeekOfMonth = @WeekOfMonth + 1 -- increment for next iteration
-- in month loop
end --End month loop.
set @QuarterOfYear = @QuarterOfYear + 1 -- increment for next iteration
-- in quarter loop
set @FirstOfQuarter = dateadd(month,3,@FirstOfQuarter) -- new FirstOfQuarter
end -- End quarter loop.
set @RunningYear = @RunningYear + 1 -- increment for next iteration
-- in year loop
end -- End year loop.
go
Using the Code
Let's check the result of populating our tables.
-- CheckResults
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.
-- CompareToOther
-- This SQL can be modified to compare specific column values of
-- THIS (vwDimDate implementation)
-- to an existing DimDate.
-- Modify table name or column name appearing under string of 'v's as needed.
-- This will show discrepancies for the columns specified in the where clause below.
-- Any differences should be reviewed and action taken as necessary.
select This.DateKey
,This.FYYear
-- vvvvvvv
,Other.DateKey -- <-- Modify!
-- vvvvvvvvvv
,Other.FiscalYear -- <-- Modify!
from vwDimDate This
join
-- vvvvvvv
DimDate Other -- <-- Modify!
-- vvvvvvv
on This.DateKey = Other.DateKey -- <-- Modify!
-- vvvvvvvvvv
where This.FYYear <> Other.FiscalYear -- <-- Modify!
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