Date Dimensions in T-SQL using CTE

Question

Date Dimensions in T-SQL using CTE

Bhudev Prakash

SSC-Addicted

Points: 471
More actions
June 14, 2010 at 3:09 pm

#236442

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

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

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

ricky70rana SSC Veteran Points: 217 More actions · Answer 1

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: 4370 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

Yimmy.Rengifo

SSC Rookie

Points: 27

July 8, 2010 at 9:08 pm

#1190588

Excellent code. Thanks ! 🙂 🙂

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

May 12, 2016 at 7:25 am

#1878602

Thanks for the script.