Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Dimensions in T-SQL using CTE Expand / Collapse
Author
Message
Posted Monday, June 14, 2010 3:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:07 AM
Points: 49, Visits: 186
Comments posted to this topic are about the item Date Dimensions in T-SQL using CTE
Post #937152
Posted Monday, July 5, 2010 2:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 1:11 AM
Points: 19, Visits: 40
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
Posted Monday, July 5, 2010 2:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 11, 2014 3:53 AM
Points: 292, Visits: 665
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
Posted Monday, July 5, 2010 11:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:07 AM
Points: 49, Visits: 186
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
Posted Monday, July 5, 2010 11:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:07 AM
Points: 49, Visits: 186
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
Posted Tuesday, July 6, 2010 3:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 1:11 AM
Points: 19, Visits: 40
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
Posted Tuesday, July 6, 2010 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:07 AM
Points: 49, Visits: 186
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
Posted Thursday, July 8, 2010 9:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 3:43 PM
Points: 1, Visits: 8
Excellent code. Thanks ! :)
Post #949695
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse