Skip to Content
avatar image
Former Member

Joining on Month& Year in Date column

I have 2  table with the below dates. SQL Server is the database. Table A 2016-03-31 2016-02-29 Table B 2016-03-1 2016-02-1 I have to join these 2 tables to get data for each month. However Inner join would fail and trying to extract month & year and joining in SQL, which is working fine. Table A.MONTH=Table B.MONTH AND Table A.YR=Table B.YR But how would i do this join in the universe level?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Apr 12, 2016 at 09:10 PM

    Hi Cristina,

    How about creating Calculated Columns for Month and Year in those tables and then join on those newly calculated columns as Table A.Month = Table B.Month and Table A.Year = Table B.Year?

    You can use Year() and Month() function to get the Month and Year number, its available by default in IDT for some databases, check out if it works for the type of database you are using.

    This can help to you create Calculated Columns using Date value:

    Information design tool 4.0: Create a calculated column using a date value

    Let us know if this helps and/or you have any questions/issues.

    Update: If you've used Universe Design Tool (UDT) to create the Universe, then you'll have to create a Derived table.

    Thanks,
    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 13, 2016 at 05:32 AM

    This is a design question rather than a technical question. Check with your DBA to decide on the best way.

    My take would be to create a Date_Dim table.

    why?

    1. Right now you have two tables what will happen if you need to add a new one later?
    2. Joining two fact tables could be tricky, especially when you are trying to link it with a data factor
    3. When to plan to bring in other dimensions it will become even more complicated, you will struggle with traps and loops.
    4. You will have a new requirement, say, to calculate sales only for Sundays, what will you do?

    How?

    I've uploaded the data in CSV format here for you to play around. (Dates are from 01-Sep-2015 to 30-March-2016)

    After creating the time_dim table (or date dim) setup your universe (unx or unv - it doesn't matter).

    Make sure that the outer join is always on the Time_Dim table.


    1.jpg (33.3 kB)
    2.jpg (98.3 kB)
    3.jpg (111.6 kB)
    Add comment
    10|10000 characters needed characters exceeded