SDU Tools: Date Dimension Period Columns in SQL Server T-SQL

The DateDimensionColumns function in our free SDU Tools for developers and DBAs, has been really popular. It provides the standard columns that are part of a typical date dimension in a data warehouse. But we're especially proud of a new function that can be used to expand a date dimension with really useful additional information. We call it DateDimensionPeriodColumns.

The DateDimensionColumns function had the usual columns:

Date, Day Number, Day Name, Short Day Name, Month Name, Short Month Name, Month Number, Month Label, Year, Year Label, Day of Year, Fiscal Month Number, Fiscal Month Label, Fiscal Year, Fiscal Year Label, Day of Fiscal Year, ISO Week Number

But the DateDimensionPeriodColumns function adds many more useful details:

Is Today, Is Yesterday, Is Tomorrow, Is Future, Is Working Day, Is Last Working Day, Is Next Working Day, Is Weekend, Is Same Month, Is Month to Date, Is Same Month Last Year, Is Month to Date Last Year, Is Same Calendar Year, Is Calendar Year to Date, Is Last Calendar Year, Is Last Calendar Year to Date, Is Same Fiscal Year, Is Same Fiscal Year to Date, Is Last Fiscal Year, Is Last Fiscal Year to Date

That should let you say bye-bye to a whole lot of tricky DAX; it's easy to just use those columns for relative or period filtering

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE FUNCTION [SDU_Tools].[DateDimensionPeriodColumns]
(
    @Date date,
    @FiscalYearStartMonth int,
    @Today date
)
RETURNS TABLE
AS
-- Function:      Returns a table (single row) of date dimension period columns
-- Parameters:    @Date date => date to process
--                @FiscalYearStartMonth int => month number when the financial year starts
--                @Today date => the current day (or the target day)
-- Action:        Returns a single row table with date dimension period columns
-- Return:        Single row rowset with date dimension period columns
-- Refer to this video: https://youtu.be/pcoaHYK70nU
--
-- Test examples: 
/*

SELECT * FROM SDU_Tools.DateDimensionPeriodColumns('20200131', 7, SYSDATETIME());

SELECT db.DateValue, ddpc.* 
FROM SDU_Tools.DatesBetween('20190201', '20200420') AS db
CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc
ORDER BY db.DateValue;

SELECT ddc.*, ddpc.* 
FROM SDU_Tools.DatesBetween('20200201', '20200401') AS db
CROSS APPLY SDU_Tools.DateDimensionColumns(db.DateValue, 7) AS ddc
CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc
ORDER BY db.DateValue;

*/
RETURN 

WITH FiscalYearDates
AS
(
    SELECT CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth
                THEN CAST(CAST(YEAR(@Today) AS varchar(4)) 
                               + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                               + '01' AS date)
                ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4)) 
                               + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                               + '01' AS date)
           END AS StartOfFiscalYear,
           DATEADD(day, -1, 
                   DATEADD(year, 1, 
                           CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth
                                THEN CAST(CAST(YEAR(@Today) AS varchar(4)) 
                                          + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                                          + '01' AS date)
                                ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4)) 
                                          + RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)
                                          + '01' AS date)
           END)) AS EndOfFiscalYear
)
SELECT CASE WHEN @Date = @Today 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsToday,
       CASE WHEN @Date = DATEADD(day, -1, @Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsYesterday,
       CASE WHEN @Date = DATEADD(day, 1, @Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsTomorrow,
       CASE WHEN @Date > @Today 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsFuture,
       CASE WHEN DATEPART(weekday, @Date) 
                 NOT IN (DATEPART(weekday, '19000106'), -- Saturday
                         DATEPART(weekday, '19000107')) -- Sunday
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsWorkingDay,
       CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000108') -- Monday
                              THEN DATEADD(day, -3, @Today)
                              ELSE DATEADD(day, -1, @Today)
                         END 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastWorkingDay,
       CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000105') -- Friday
                              THEN DATEADD(day, 3, @Today)
                              ELSE DATEADD(day, 1, @Today)
                         END 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsNextWorkingDay,
       CASE WHEN DATEPART(weekday, @Date) 
                 IN (DATEPART(weekday, '19000106'), -- Saturday
                     DATEPART(weekday, '19000107')) -- Sunday
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsWeekend,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameMonth,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)
                                              AND DAY(@Date) BETWEEN 1 AND DAY(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsMonthToDate,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameMonthLastYear,
       CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)
                                              AND DAY(@Date) BETWEEN 1 AND DAY(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsMonthToDateLastYear,
       CASE WHEN YEAR(@Date) = YEAR(@Today)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameCalendarYear,
       CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) AS varchar(4)) + '0101' AS date)
                       AND @Today 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsCalendarYearToDate,
       CASE WHEN YEAR(@Date) = (YEAR(@Today) - 1)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastCalendarYear,
       CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) - 1 AS varchar(4)) + '0101' AS date)
                       AND DATEADD(year, -1, @Today) 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastCalendarYearToDate,
       CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND fyd.EndOfFiscalYear 
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameFiscalYear,
       CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND @Date  
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsSameFiscalYearToDate,
       CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear) 
                       AND DATEADD(year, -1, fyd.EndOfFiscalYear)
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastFiscalYear,
       CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear) 
                       AND DATEADD(year, -1, @Date)  
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END AS IsLastFiscalYearToDate
FROM FiscalYearDates AS fyd;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

CREATE FUNCTION [SDU_Tools].[DateDimensionPeriodColumns]

(

@Date date,

@FiscalYearStartMonth int,

@Today date

)

RETURNS TABLE

-- Function: Returns a table (single row) of date dimension period columns

-- Parameters: @Date date => date to process

-- @FiscalYearStartMonth int => month number when the financial year starts

-- @Today date => the current day (or the target day)

-- Action: Returns a single row table with date dimension period columns

-- Return: Single row rowset with date dimension period columns

-- Refer to this video: https://youtu.be/pcoaHYK70nU

-- Test examples:

SELECT * FROM SDU_Tools.DateDimensionPeriodColumns('20200131', 7, SYSDATETIME());

SELECT db.DateValue, ddpc.*

FROM SDU_Tools.DatesBetween('20190201', '20200420') AS db

CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc

ORDER BY db.DateValue;

SELECT ddc.*, ddpc.*

FROM SDU_Tools.DatesBetween('20200201', '20200401') AS db

CROSS APPLY SDU_Tools.DateDimensionColumns(db.DateValue, 7) AS ddc

CROSS APPLY SDU_Tools.DateDimensionPeriodColumns(db.DateValue, 7, SYSDATETIME()) AS ddpc

ORDER BY db.DateValue;

RETURN

WITH FiscalYearDates

(

SELECT CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth

THEN CAST(CAST(YEAR(@Today) AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

END AS StartOfFiscalYear,

DATEADD(day, -1,

DATEADD(year, 1,

CASE WHEN MONTH(@Today) >= @FiscalYearStartMonth

THEN CAST(CAST(YEAR(@Today) AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

ELSE CAST(CAST(YEAR(@Today) - 1AS varchar(4))

+ RIGHT('00' + CAST(@FiscalYearStartMonth AS varchar(2)), 2)

+ '01' AS date)

END)) AS EndOfFiscalYear

)

SELECT CASE WHEN @Date = @Today

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsToday,

CASE WHEN @Date = DATEADD(day, -1, @Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsYesterday,

CASE WHEN @Date = DATEADD(day, 1, @Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsTomorrow,

CASE WHEN @Date > @Today

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsFuture,

CASE WHEN DATEPART(weekday, @Date)

NOT IN (DATEPART(weekday, '19000106'), -- Saturday

DATEPART(weekday, '19000107')) -- Sunday

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsWorkingDay,

CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000108') -- Monday

THEN DATEADD(day, -3, @Today)

ELSE DATEADD(day, -1, @Today)

END

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastWorkingDay,

CASE WHEN @Date = CASE WHEN DATEPART(weekday, @Today) = DATEPART(weekday, '19000105') -- Friday

THEN DATEADD(day, 3, @Today)

ELSE DATEADD(day, 1, @Today)

END

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsNextWorkingDay,

CASE WHEN DATEPART(weekday, @Date)

IN (DATEPART(weekday, '19000106'), -- Saturday

DATEPART(weekday, '19000107')) -- Sunday

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsWeekend,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameMonth,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = YEAR(@Today)

AND DAY(@Date) BETWEEN 1 AND DAY(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsMonthToDate,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameMonthLastYear,

CASE WHEN MONTH(@Date) = MONTH(@Today) AND YEAR(@Date) = (YEAR(@Today) - 1)

AND DAY(@Date) BETWEEN 1 AND DAY(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsMonthToDateLastYear,

CASE WHEN YEAR(@Date) = YEAR(@Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameCalendarYear,

CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) AS varchar(4)) + '0101' AS date)

AND @Today

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsCalendarYearToDate,

CASE WHEN YEAR(@Date) = (YEAR(@Today) - 1)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastCalendarYear,

CASE WHEN @Date BETWEEN CAST(CAST(YEAR(@Date) - 1 AS varchar(4)) + '0101' AS date)

AND DATEADD(year, -1, @Today)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastCalendarYearToDate,

CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND fyd.EndOfFiscalYear

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameFiscalYear,

CASE WHEN @Date BETWEEN fyd.StartOfFiscalYear AND @Date

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsSameFiscalYearToDate,

CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear)

AND DATEADD(year, -1, fyd.EndOfFiscalYear)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastFiscalYear,

CASE WHEN @Date BETWEEN DATEADD(year, -1, fyd.StartOfFiscalYear)

AND DATEADD(year, -1, @Date)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END AS IsLastFiscalYearToDate

FROM FiscalYearDates AS fyd;

Find out more

Latest version of the code

Leave a Reply Cancel reply