Post reply

Date in Date Time Dimension not ordering correctly

  • November 2, 2012 at 5:00 am

    #264229

    I have the following in Date time dimension

    ID Date

    2011100606/10/2011

    2011100707/10/2011

    2011100808/10/2011

    2011100909/10/2011

    2011101010/10/2011

    2011101111/10/2011

    2011101212/10/2011

    For Date, In properties its ordered by the ID. the Time is Calendar > date

    After a process its still ordering totally incorrectly, which means its not working for MDX date ranges etc.

    Has anyone got any ideas why this hasn't worked and the date doesn't order correctly?

    Thanks

    Debbie

  • November 2, 2012 at 6:10 am

    #1555422

    Did you incrementally process it? (A full process may be required)

    Also:

    What order are the dates in?

    Is it just the date that is in the wrong order or any months or years?

    Is the key defined as an integer in the cube?

    Mack

  • November 2, 2012 at 6:37 am

    #1555434

    Ive sorted it 🙂

    The user friendly dates dont order properly. In the data source Ive created any OrderBy column that sorts.

    Then using this http://dinesql.blogspot.co.uk/2011/04/ordering-attribute-members-of.html

    I sent this up as the OrderByAttribute.

    And now my dates are all in the right order. Result!

    Thanks 🙂

  • November 2, 2012 at 6:41 am

    #1555436

    I wouldn't have thought you'd need to do that if the date format was an integer yyyymmdd and you are ordering by the key

    Still if it works then job's a good 'un'

    Mack

  • November 2, 2012 at 7:00 am

    #1555444

    My source dates have been amended to how the user wants to view them CONVERT(VarChar(50), [Date], 103) AS [Date] Perfect for the user. Rubbish for the order by.

    This is the SQL Ive used to create the perfect date int

    CAST(CAST(DATEPART(year,Statement_First_Issued_Date)as varchar(10))+

    CASE WHEN LEN(CAST(DATEPART(Month,Statement_First_Issued_Date)as varchar(10))) = 1

    THEN '0'+CAST(DATEPART(Month,Statement_First_Issued_Date)as varchar(10))

    ELSE CAST(DATEPART(Month,Statement_First_Issued_Date)as varchar(10))END+

    CASE WHEN LEN(CAST(DATEPART(day,Statement_First_Issued_Date)as varchar(10))) = 1

    THEN '0'+CAST(DATEPART(day,Statement_First_Issued_Date)as varchar(10))

    ELSE CAST(DATEPART(day,Statement_First_Issued_Date)as varchar(10))END

    as int)AS Date_OrderBy

    Its a bit unwieldly but for the time being it works.Phew

  • November 2, 2012 at 8:02 am

    #1555488

    To convert a date to yyyymmdd you could use convert like in the following example:

    select Cast(convert(varchar, getdate(), 112) as Int)

    Mack

  • November 2, 2012 at 8:04 am

    #1555489

    Well THATS a thousand times easier!!

    Thank you 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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