• Channels

  • Contact

  • Main Site

  • More

    Use tab to navigate through the menu items.
    To see this working, head to your live site.
    • Categories
    • All Posts
    • My Posts
    merv
    Feb 07, 2018

    Azure DW - Rolling Dates From A Static Calendar?

    in Software Engineering

    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;

    0 comments
    0
    0 comments
    • Twitter Social Icon
    • LinkedIn Social Icon
    • Facebook Social Icon

    Visit the Elastacloud website