Date table with Comprehensive To Date Fields

[intapiuser]

This article outlines how to create a date table that flags whether or not a particular date is within a particular range, based off of the current date. For example, if today is the 100th day of the year, we may want to analyze information for the first 100 days of the past several years. 

The flags can identify if it is up to the current

• Day of the week
• Week of the Year
• Day of the Month
• Day of the Quarter
• Day of the Year

Implementation

This will be implemented as a table created via a custom SQL expression in the Elasticube. This expression uses a date table, which is described and can be downloaded under this Date Dimension File Article. Additionally, it joins against the data table that is being used for analysis, to only contain the necessary dates.

The following code is used to define the date table

SELECT a.*,
CASE WHEN a.DayNumOfWeek<=t.DayNumOfWeek THEN 'Yes' ELSE 'No' END AS [Is Week to Date?],
CASE WHEN a.WeekNum<=t.WeekNum THEN 'Yes' ELSE 'No' END AS [Is Week of Year to Date?],
CASE WHEN a.DayNumOfMonth<=t.DayNumOfMonth THEN 'Yes' ELSE 'No' END AS [Is Month to Date?],
CASE WHEN a.DayNumOfQuarter<=t.DayNumOfQuarter THEN 'Yes' ELSE 'No' END AS [Is Quarter to Date?],
CASE WHEN a.DayNumOfYear<=t.DayNumOfYear THEN 'Yes' ELSE 'No' END AS [Is Year to Date?]
FROM [Date Dimension.csv] a
--Establish the necessary date range
INNER JOIN (SELECT MIN(s.OrderDate) AS Min_Date, MAX(s.OrderDate) AS Max_Date FROM [SalesOrderHeader] s) mm
 ON a.Date BETWEEN mm.Min_Date AND mm.Max_Date
--Find date information for the current day
CROSS JOIN
 (SELECT d.date, d.DayNumOfMonth, d.DayNumOfQuarter, d.DayNumOfYear, d.WeekNum, d.DayNumOfWeek
 FROM [Date Dimension.csv] d
 WHERE d.Date=CreateDate(GetYear(Now()), GetMonth(Now()), GetDay(Now()))) t