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;
2020-05-13