robjlong
12/9/2015 - 3:43 PM

Date Dimension Table Value Function. Returns the date details as it relates to a specific date. This is particularly helpful in an inferre

Date Dimension Table Value Function. Returns the date details as it relates to a specific date. This is particularly helpful in an inferred member scenario when future dates outside of the scope of the date dimension are sourced.

SET QUOTED_IDENTIFIER ON;
GO
-- [Common].[DateContent]
-- 
-- Returns a table with the content for a specified date
--
--  Date Dimension Creation:	https://gist.github.com/daveduvarney/663bbe3aa85c16a72023
--	Date Dimension Populate:	https://gist.github.com/daveduvarney/605f98fce4fedc9e1db2
--
--
-- SELECT * FROM [Common].[DateContent]('1/1/1950')
--
--------------------------------------------------------------------------------
-- Change History
--------------------------------------------------------------------------------
-- Version  Date       	Author				Description
-- -------  ---------- 	-----------			------------------------------------
--  1.0		12/09/2015	  rob.long			Initial Creation
--------------------------------------------------------------------------------
CREATE FUNCTION [Common].[DateContent] ( @FullDate DATE )
RETURNS @DateDetail TABLE
    (
      [DateKey] [INT] NOT NULL
    , [DateSequence] [INT] NOT NULL
    , [AS400Date] [INT] NOT NULL
    , [ISODate] INT NOT NULL
    , [FullDate] [DATETIME] NOT NULL
    , [DisplayDate] [VARCHAR](20) NOT NULL
    , [DayOfWeekNumber] [TINYINT] NOT NULL
    , [DayOfWeekName] [VARCHAR](10) NOT NULL
    , [DayOfWeekShortName] [VARCHAR](3) NOT NULL
    , [CalendarDayOfYear] [SMALLINT] NOT NULL
    , [CalendarWeekKey] [INT] NOT NULL
    , [CalendarWeekSequence] [INT] NOT NULL
    , [CalendarWeekNumber] [TINYINT] NOT NULL
    , [CalendarWeekName] [VARCHAR](7) NOT NULL
    , [CalendarWeekShortName] [VARCHAR](3) NOT NULL
    , [CalendarWeekYear] [VARCHAR](20) NOT NULL
    , [CalendarMonthKey] [INT] NOT NULL
    , [CalendarMonthSequence] [INT] NOT NULL
    , [CalendarMonthNumber] [TINYINT] NOT NULL
    , [CalendarMonthName] [VARCHAR](10) NOT NULL
    , [CalendarMonthShortName] [VARCHAR](3) NOT NULL
    , [CalendarMonthYear] [VARCHAR](20) NOT NULL
    , [CalendarQuarterKey] [INT] NOT NULL
    , [CalendarQuarterSequence] [INT] NOT NULL
    , [CalendarQuarterNumber] [TINYINT] NOT NULL
    , [CalendarQuarterName] [VARCHAR](10) NOT NULL
    , [CalendarQuarterShortName] [VARCHAR](2) NOT NULL
    , [CalendarQuarterYear] [VARCHAR](20) NOT NULL
    , [CalendarYear] [SMALLINT] NOT NULL
    , [FiscalDayOfYear] [SMALLINT] NOT NULL
    , [FiscalWeekKey] [INT] NOT NULL
    , [FiscalWeekSequence] [INT] NOT NULL
    , [FiscalWeekNumber] [TINYINT] NOT NULL
    , [FiscalWeekName] [VARCHAR](14) NOT NULL
    , [FiscalWeekShortName] [VARCHAR](4) NOT NULL
    , [FiscalWeekYear] [VARCHAR](13) NOT NULL
    , [FiscalMonthKey] [INT] NOT NULL
    , [FiscalMonthSequence] [INT] NOT NULL
    , [FiscalMonthNumber] [TINYINT] NOT NULL
    , [FiscalMonthName] [VARCHAR](10) NOT NULL
    , [FiscalMonthShortName] [VARCHAR](3) NOT NULL
    , [FiscalMonthYear] [VARCHAR](20) NOT NULL
    , [FiscalQuarterKey] [INT] NOT NULL
    , [FiscalQuarterSequence] [INT] NOT NULL
    , [FiscalQuarterNumber] [TINYINT] NOT NULL
    , [FiscalQuarterName] [VARCHAR](20) NOT NULL
    , [FiscalQuarterShortName] [VARCHAR](4) NOT NULL
    , [FiscalQuarterYear] [VARCHAR](20) NOT NULL
    , [FiscalYear] [SMALLINT] NOT NULL
    , [IsWeekDay] [BIT] NOT NULL
    , [IsHoliday] [BIT] NOT NULL
    , [HolidayName] [VARCHAR](20) NULL
    , [IsCurrentDay] [BIT] NOT NULL
    , [CurrentDayOffset] [INT] NOT NULL
    , [CurrentCalendarWeekOffset] [INT] NOT NULL
    , [CurrentCalendarMonthOffset] [INT] NOT NULL
    , [CurrentCalendarQuarterOffset] [INT] NOT NULL
    , [CurrentCalendarYearOffset] [INT] NOT NULL
    , [CurrentFiscalWeekOffset] [INT] NOT NULL
    , [CurrentFiscalMonthOffset] [INT] NOT NULL
    , [CurrentFiscalQuarterOffset] [INT] NOT NULL
    , [CurrentFiscalYearOffset] [INT] NOT NULL
    )
AS
    BEGIN
        DECLARE @IncrementDate DATETIME;

        SET @IncrementDate = @FullDate;


        DECLARE @Year SMALLINT
          , @Quarter TINYINT
          , @Month TINYINT
          , @Day TINYINT
          , @Week TINYINT
          , @CalendarWeekKey INT
          , @CalendarMonthKey INT
          , @CalendarQuarterKey INT
          , @FiscalWeekKey INT
          , @FiscalMonthKey INT
          , @FiscalQuarterKey INT
          , @LastCalendarWeekKey INT
          , @LastCalendarMonthKey INT
          , @LastCalendarQuarterKey INT
          , @LastFiscalWeekKey INT
          , @LastFiscalMonthKey INT
          , @LastFiscalQuarterKey INT;
        DECLARE @CalendarWeekSequence INT = 1
          , @CalendarMonthSequence INT = 1
          , @CalendarQuarterSequence INT = 1
          , @FiscalWeekSequence INT = 1
          , @FiscalMonthSequence INT = 1
          , @FiscalQuarterSequence INT = 1;


        SET @Year = YEAR(@IncrementDate);
        SET @Quarter = DATEPART(q, @IncrementDate);
        SET @Month = MONTH(@IncrementDate);
        SET @Day = DAY(@IncrementDate);
        SET @Week = DATEPART(wk, @IncrementDate);
        SET @CalendarWeekKey = ( @Year * 100 ) + @Week;
        SET @CalendarMonthKey = ( @Year * 100 ) + @Month;
        SET @CalendarQuarterKey = ( @Year * 100 ) + @Quarter;	
        SET @FiscalWeekKey = ( @Year * 100 ) + @Week;
        SET @FiscalMonthKey = ( @Year * 100 ) + @Month;
        SET @FiscalQuarterKey = ( @Year * 100 ) + @Quarter;	

	--Set the sequence values
        SET @CalendarWeekSequence = CASE WHEN @CalendarWeekKey = @LastCalendarWeekKey THEN @CalendarWeekSequence
                                         ELSE @CalendarWeekSequence + 1
                                    END;
        SET @CalendarMonthSequence = CASE WHEN @CalendarMonthKey = @LastCalendarMonthKey THEN @CalendarMonthSequence
                                          ELSE @CalendarMonthSequence + 1
                                     END;
        SET @CalendarQuarterSequence = CASE WHEN @CalendarQuarterKey = @LastCalendarQuarterKey THEN @CalendarQuarterSequence
                                            ELSE @CalendarQuarterSequence + 1
                                       END;
        SET @FiscalWeekSequence = CASE WHEN @FiscalWeekKey = @LastFiscalWeekKey THEN @FiscalWeekSequence
                                       ELSE @FiscalWeekSequence + 1
                                  END;
        SET @FiscalMonthSequence = CASE WHEN @FiscalMonthKey = @LastFiscalMonthKey THEN @FiscalMonthSequence
                                        ELSE @FiscalMonthSequence + 1
                                   END;
        SET @FiscalQuarterSequence = CASE WHEN @FiscalQuarterKey = @LastFiscalQuarterKey THEN @FiscalQuarterSequence
                                          ELSE @FiscalQuarterSequence + 1
                                     END;
	
        INSERT  INTO @DateDetail
                SELECT  ( @Year * 10000 ) + ( @Month * 100 ) + @Day						--DateKey
                      , CAST(CAST(@IncrementDate AS DATETIME) AS INT)				--DateSequence
                      , CAST(CONVERT(VARCHAR(10), @IncrementDate, 112) AS INT) - 19000000	--XADate		
                      , CAST(CONVERT(VARCHAR(10), @IncrementDate, 112) AS INT) 	--IsoDate		
                      , @IncrementDate												--FullDate
                      , CONVERT(VARCHAR, @IncrementDate, 101)						--DisplayDate
                      , DATEPART(dw, @IncrementDate)								--DayOfWeekNumber
                      , DATENAME(dw, @IncrementDate)								--DayOfWeekName
                      , LEFT(DATENAME(dw, @IncrementDate), 3)						--DayOfWeekShortName
                      , DATEPART(dy, @IncrementDate)								--CalendarDayOfYear
                      , @CalendarWeekKey											--CalendarWeekKey
                      , @CalendarWeekSequence									--CalendarWeekSequence
                      , @Week														--CalendarWeekNumber
                      , 'Week ' + RIGHT('0' + CAST(@Week AS VARCHAR), 2)			--CalendarWeekName
                      , 'W' + RIGHT('0' + CAST(@Week AS VARCHAR), 2)				--CalendarWeekShortName
                      , 'W' + RIGHT('0' + CAST(@Week AS VARCHAR), 2) + ' - ' + CAST(@Year AS VARCHAR) --CalendarWeekYear
                      , @CalendarMonthKey											--CalendarMonthKey
                      , @CalendarMonthSequence								--CalendarMonthSequence
                      , @Month														--CalendarMonthNumber
                      , DATENAME(m, @IncrementDate)								--CalendarMonthName
                      , LEFT(DATENAME(m, @IncrementDate), 3)						--CalendarMonthShortName
                      , LEFT(DATENAME(m, @IncrementDate), 3) + ' - ' + CAST(@Year AS VARCHAR) --CalendarMonthYear
                      , @CalendarQuarterKey										--CalendarQuarterKey
                      , @CalendarQuarterSequence								--CalendarQuarterSequence
                      , @Quarter													--CalendarQuarterNumber
                      , 'Quarter ' + CAST(@Quarter AS VARCHAR)						--CalendarQuarterName
                      , 'Q' + CAST(@Quarter AS VARCHAR)							--CalendarQuarterShortName
                      , 'Q' + CAST(@Quarter AS VARCHAR) + ' - ' + CAST(@Year AS VARCHAR) --CalendarQuarterYear
                      , @Year														--CalendarYear
                      , DATEPART(dy, @IncrementDate)								--FiscalDayOfYear
                      , @FiscalWeekKey												--FiscalWeekKey
                      , @FiscalWeekSequence									--FiscalWeekSequence
                      , @Week														--FiscalWeekNumber
                      , 'Week ' + RIGHT('0' + CAST(@Week AS VARCHAR), 2)			--FiscalWeekName
                      , 'W' + RIGHT('0' + CAST(@Week AS VARCHAR), 2)				--FiscalWeekShortName
                      , 'W' + RIGHT('0' + CAST(@Week AS VARCHAR), 2) + ' - ' + CAST(@Year AS VARCHAR) --FiscalWeekYear
                      , @FiscalMonthKey											--FiscalMonthKey
                      , @FiscalMonthSequence									--FiscalMonthSequence
                      , @Month														--FiscalMonthNumber
                      , DATENAME(m, @IncrementDate)								--FiscalMonthName
                      , LEFT(DATENAME(m, @IncrementDate), 3)						--FiscalMonthShortName
                      , LEFT(DATENAME(m, @IncrementDate), 3) + ' - ' + CAST(@Year AS VARCHAR) --FiscalMonthYear
                      , @FiscalQuarterKey											--FiscalQuarterKey
                      , @FiscalQuarterSequence								--FiscalQuarterSequence
                      , @Quarter													--FiscalQuarterNumber
                      , 'Fiscal Quarter ' + CAST(@Quarter AS VARCHAR)				--FiscalQuarterName
                      , 'FQ' + CAST(@Quarter AS VARCHAR)							--FiscalQuarterShortName
                      , 'FQ' + CAST(@Quarter AS VARCHAR) + ' - ' + CAST(@Year AS VARCHAR) --FiscalQuarterYear
                      , @Year														--FiscalYear
                      , CASE WHEN DATENAME(dw, @IncrementDate) = 'Saturday'
                                  OR DATENAME(dw, @IncrementDate) = 'Sunday' THEN 0
                             ELSE 1
                        END --IsWeekDay
                      , 0															--IsHoliday
                      , ''															--HolidayName
                      , 0															--IsCurrentDay
                      , 0															--CurrentDayOffset
                      , 0															--CurrentCalendarWeekOffset
                      , 0															--CurrentCalendarMonthOffset
                      , 0															--CurrentCalendarQuarterOffset
                      , 0															--CurrentCalendarYearOffset
                      , 0															--CurrentFiscalWeekOffset	
                      , 0															--CurrentFiscalMonthOffset
                      , 0															--CurrentFiscalQuarterOffset
                      , 0;															--CurrentFiscalYearOffset
	
        RETURN; 
    END;
GO