Date Dimensions in T-SQL using CTE

  • Bhudev Prakash

    SSC-Addicted

    Points: 471

    Comments posted to this topic are about the item Date Dimensions in T-SQL using CTE

  • ricky70rana

    SSC Veteran

    Points: 217

    Your code is not working as tried to execute in SQL SERVER 2008. Its showing some syntax error in the code section given below :

    SET DATEFORMAT MDY;

    DECLARE @StartDate DATETIME = '01-01-2010';

    DECLARE @EndDate DATETIME = '31-12-2020';

    WITH DateCTE AS

    (

    ????SELECT TimeKey = CONVERT(INT,(CONVERT(VARCHAR(10),@StartDate,112))),

    ???????? FullDate = @StartDate

    ????UNION ALL

    ????SELECT

    ?????? TimeKey = ?CONVERT(INT,(CONVERT(NVARCHAR(10),FullDate + 1,112))),

    ????????FullDate = FullDate + 1

    ????FROM DateCTE

    ????WHERE FullDate + 1 < = @EndDate

    )

    SELECT * FROM DateCTE ;

  • wildh

    SSCarpal Tunnel

    Points: 4260

    I always read articles about date and time dimensions; just to see how they compare to the script I take with me wherever I go, just to see if there’s something else I could incorporate or do different. Recently (based on articles I’ve seen here, apart form ‘The Sins of Old: Time_T’, which was fun reading the discussion) I’ve been questioning some of the things I include in my date and time dimension.

    Does anyone else include descriptions apart form day and month? I know descriptions can be constructed in analysis services when creating the dim but I choose to keep descriptions like ‘Fri 01/01/2010’ and ‘Jan 2010’ and even ‘11:59 pm - 12:00 am’ in the relative date and time dimension and include that field as the dimension description. I didn’t choose this approach for performance or disc space I chose it for personal preference.

    So I was wondering does anyone else keep their dimension ‘long descriptions’ in their SQL tables and if not is there a reason?

  • Bhudev Prakash

    SSC-Addicted

    Points: 471

    Hi,

    These are the points based on that you may getting error meesage:

    1. DATEFORMAT is set to MDY and you are storing as date in DMY format.

    2. You may use OPTION (MAXRECURSION 0) opetion to avoid following error message, as default recursion is 100.

    Msg 530, Level 16, State 1, Line 5

    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    Please check following, it should work for you.

    SET DATEFORMAT DMY;

    DECLARE @StartDate DATETIME = '01-01-2010';

    DECLARE @EndDate DATETIME = '31-12-2010';

    WITH DateCTE AS

    (

    SELECT TimeKey = CONVERT(INT,(CONVERT(VARCHAR(10),@StartDate,112))),

    FullDate = @StartDate

    UNION ALL

    SELECT

    TimeKey = CONVERT(INT,(CONVERT(NVARCHAR(10),FullDate + 1,112))),

    FullDate = FullDate + 1

    FROM DateCTE

    WHERE FullDate + 1 < = @EndDate

    )

    SELECT * FROM DateCTE

    OPTION (MAXRECURSION 0);

    I hope it will help.

  • Bhudev Prakash

    SSC-Addicted

    Points: 471

    Hi wildh

    As I included URLs of several Time Dimension discussions, I think all are good enough to produce Time Dim, and infect I picked my base query one of them only and enhance little bit as per my need.

    Regarding

    Does anyone else include descriptions apart form day and month? I know descriptions can be constructed in analysis services when creating the dim but I choose to keep descriptions like ‘Fri 01/01/2010’ and ‘Jan 2010’ and even ‘11:59 pm - 12:00 am’ in the relative date and time dimension and include that field as the dimension description. I didn’t choose this approach for performance or disc space I chose it for personal preference.

    It depends on Project-to-Project requirement how it requires displaying the description, someone will like

    JAN 2010

    JAN – 2010

    2010 - JAN

    January 2010

    January – 2010

    2010 - January

    And again depends on their grain level, may be Week, may be Month or may be bi-weekly. So as per my understanding, it is based on requirement at which level it requires to see description and in what format. On the other hand, in retail industry, client may give you their own rule for showing descriptions, hence is better to keep base Time table and top of that VIEW or in SSAS description can be added as per decision/design.

  • ricky70rana

    SSC Veteran

    Points: 217

    I tried to execute the code again but still its not working

    Now Its Giving error in the section given below

    INSERT INTO DimTime

    SELECT

    TimeKey,

    ????TimeFullDate,

    ????TimeDayNumberOfWeek,

    especially for TimeKey and TimeFullDate

  • Bhudev Prakash

    SSC-Addicted

    Points: 471

    Hi Ricky,

    Please remove special characters and then check, it should work, When I put my code, it was tab for me, but it seems that Script editor has changed it to something else.

    Regards

    Bhudev

  • Yimmy.Rengifo

    SSC Rookie

    Points: 27

    Excellent code. Thanks ! 🙂 🙂

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply