Date Dimensions in T-SQL using CTE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bhudev Prakash » Date Dimensions in T-SQL using CTE

Date Dimensions in T-SQL using CTE

Rate Topic

Display Mode

Topic Options

Author

Message

Bhudev Prakash

Posted Monday, June 14, 2010 3:09 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:00 AM
Points: 49, Visits: 168

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

Post #937152

ricky70rana

Posted Monday, July 05, 2010 2:49 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 07, 2011 11:58 PM
Points: 19, Visits: 38

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 ;

Post #947412

wildh

Posted Monday, July 05, 2010 2:57 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:08 AM
Points: 292, Visits: 657

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?

Post #947415

Bhudev Prakash

Posted Monday, July 05, 2010 11:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:00 AM
Points: 49, Visits: 168

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.

Post #947695

Bhudev Prakash

Posted Monday, July 05, 2010 11:29 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:00 AM
Points: 49, Visits: 168

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

[color=#345]

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.

[/color]

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.

Post #947705

ricky70rana

Posted Tuesday, July 06, 2010 3:08 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 07, 2011 11:58 PM
Points: 19, Visits: 38

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

Post #947772

Bhudev Prakash

Posted Tuesday, July 06, 2010 7:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:00 AM
Points: 49, Visits: 168

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

Post #947899

Yimmy.Rengifo

Posted Thursday, July 08, 2010 9:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2013 7:23 AM
Points: 1, Visits: 6

Excellent code. Thanks ! Smile

Post #949695

« Prev Topic | Next Topic »

Permissions