One of the requirements we most often come across when working on solutions involving Azure Analysis Services is for rolling date time values so that the end user can filter their data based on calendar-based sequences (e.g. rolling day, week, month or year sequences).
I have found that one of the simplest methods to achieve this is to combine a static calendar table with date dimensional values with SQL views to generate the rolling set of sequences. Users can then incorporate this information into case statements, pick lists, filtering or slicing of data, such as Day Sequence between -7 and -1 for a rolling 7 days, or Year Sequence = 0 for the current year.
I have created a couple of samples in SQL for creating a simple calendar table in Azure SQL Data Warehouse which is configured with a distribution type of replicate, so please feel free to use.
View SQL is
/*
Name - V D Date
Description - calendar view with functions for use in models
Use any additional SQL Date/Time functions as required in the view
The sample ones I have added are in Bold
*/
DROP VIEW [dbo].[V_D_Date]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [V_D_Date] AS
(
SELECT
[DateKey]
,[Date]
,[Day]
,[DaySuffix] as Day_Suffix
,[Weekday]
,[WeekDayName] as Weekday_Name
,[DayOfYear] as Day_Of_Year
,[WeekOfMonth] as Week_Of_Month
,[WeekOfYear] as Week_Of_Year
,[ISOWeekOfYear] as ISO_Week_Of_Year
,[Month]
,[MonthName] as Month_Name
,[Quarter]
,[QuarterName] as Quarter_Name
,[Year]
,[MMYYYY]
,[YYYYMM]
,[MonthYear] as Month_Year
,[FirstDayOfMonth]
,[LastDayOfMonth]
,[FirstDayOfQuarter]
,[LastDayOfQuarter]
,[FirstDayOfYear]
,[LastDayOfYear]
,[FirstDayOfNextMonth]
,[FirstDayOfNextYear]
,DATEDIFF (DAY,GETDATE(),[Date]) AS Day_Sequence
,DATEDIFF (WEEK,GETDATE(),[Date]) AS Week_Sequence
,DATEDIFF (MONTH,GETDATE(),[Date]) AS Month_Sequence
,DATEDIFF (YEAR,GETDATE(),[Date]) AS Year_Sequence
,DATEDIFF (q,GETDATE(),[Date]) AS Qtr_Sequence
FROM [dbo].[Calendar]
)
Calendar Creation SQL
/*
Creates a table called DBO.Calendar For numbers of years fro a stated start date.
Change the table name for 'CREATE TABLE' statement.
Change date range on First Line for declare statement.
*/
DECLARE @StartDate DATE = '20160101', @NumberOfYears INT = 5;
/*this is a holding table for intermediate calculations:*/
CREATE TABLE #dimdate
(
[date] DATE,
[day] tinyint,
[month] tinyint,
FirstOfMonth date,
[MonthName] varchar(12),
[week] tinyint,
[ISOweek] tinyint,
[DayOfWeek] tinyint,
[quarter] tinyint,
[year] smallint,
FirstOfYear date,
Style112 char(8),
Style101 char(10)
);
-- prevent set or regional settings from interfering with table
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
/*use catalogue views to generate as many rows as required for dates*/
INSERT #dimdate([date])
SELECT d
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) AS x
) AS y;
UPDATE #DimDate
set
[day] = DATEPART(DAY, [date]),
[month] = DATEPART(MONTH, [date]),
FirstOfMonth = CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
[MonthName] = DATENAME(MONTH, [date]),
[week] = DATEPART(WEEK, [date]),
[ISOweek] = DATEPART(ISO_WEEK, [date]),
[DayOfWeek] = DATEPART(WEEKDAY, [date]),
[quarter] = DATEPART(QUARTER, [date]),
[year] = DATEPART(YEAR, [date]),
FirstOfYear = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),
Style112 = CONVERT(CHAR(8), [date], 112),
Style101 = CONVERT(CHAR(10), [date], 101)
;
/* CHANGE OF TABLE NAME CAN BE MADE BELOW IF REQUIRED */
--DROP TABLE dbo.Calendar
CREATE TABLE dbo.Calendar
WITH
(
DISTRIBUTION = REPLICATE
)
AS
SELECT
DateKey = CONVERT(INT, Style112),
[Date] = [date],
[Day] = CONVERT(TINYINT, [day]),
DaySuffix = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE
CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd' ELSE 'th' END END),
[Weekday] = CONVERT(TINYINT, [DayOfWeek]),
[WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
[DayOfYear] = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
WeekOfMonth = CONVERT(TINYINT, DENSE_RANK() OVER (PARTITION BY [year], [month] ORDER BY [week])),
WeekOfYear = CONVERT(TINYINT, [week]),
ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
[Month] = CONVERT(TINYINT, [month]),
[MonthName] = CONVERT(VARCHAR(10), [MonthName]),
[Quarter] = CONVERT(TINYINT, [quarter]),
QuarterName = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First'
WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END),
[Year] = [year],
MMYYYY = CONVERT(CHAR(6), LEFT(Style101, 2) + LEFT(Style112, 4)),
YYYYMM = CONVERT(CHAR(6), LEFT(Style112, 4) + LEFT(Style101, 2)),
MonthYear = CONVERT(CHAR(8), LEFT([MonthName], 3) + ' ' + LEFT(Style112, 4)),
FirstDayOfMonth = FirstOfMonth,
LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),
FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),
LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),
FirstDayOfYear = FirstOfYear,
LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),
FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)
FROM #dimdate
;
DROP Table #dimdate;