Click here to Skip to main content
15,867,834 members
Articles / Database Development / SQL Server

DimDate Revisited (The Power of a View and Nested Whiles)

Rate me:
Please Sign up or sign in to vote.
4.75/5 (4 votes)
10 Dec 2017CPOL6 min read 7.6K   8   4  
Straightforward generation of and ensuring accuracy for DimDate values
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.

  1. 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.
  2. 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.

SQL
-- 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.

SQL
-- 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.

SQL
-- 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.

SQL
-- 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.

SQL
-- 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Syncratec Solutions
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --