SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bhudev Prakash / Date Dimensions in T-SQL using CTE / Latest Posts

RE: Date Dimensions in T-SQL using CTE

Yimmy.Rengifo — Thu, 08 Jul 2010 21:08:27 GMT

Excellent code. Thanks ! :-) :)

RE: Date Dimensions in T-SQL using CTE

Bhudev Prakash — Tue, 06 Jul 2010 07:05:14 GMT

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.RegardsBhudev

RE: Date Dimensions in T-SQL using CTE

ricky70rana — Tue, 06 Jul 2010 03:08:01 GMT

I tried to execute the code again but still its not working Now Its Giving error in the section given belowINSERT INTO DimTimeSELECT TimeKey, TimeFullDate, TimeDayNumberOfWeek,especially for TimeKey and TimeFullDate

RE: Date Dimensions in T-SQL using CTE

Bhudev Prakash — Mon, 05 Jul 2010 23:29:55 GMT

Hi wildhAs 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[color=#345][quote]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.[/quote][/color]It depends on Project-to-Project requirement how it requires displaying the description, someone will like JAN 2010JAN – 20102010 - JANJanuary 2010January – 20102010 - JanuaryAnd 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.

RE: Date Dimensions in T-SQL using CTE

Bhudev Prakash — Mon, 05 Jul 2010 23:03:51 GMT

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 5The 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.

RE: Date Dimensions in T-SQL using CTE

wildh — Mon, 05 Jul 2010 02:57:38 GMT

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?

RE: Date Dimensions in T-SQL using CTE

ricky70rana — Mon, 05 Jul 2010 02:49:34 GMT

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 ;

Date Dimensions in T-SQL using CTE

Bhudev Prakash — Mon, 14 Jun 2010 15:09:35 GMT

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