1 Reply Latest reply: Aug 17, 2016 9:38 AM by Marcus Sommer RSS

    HR Data modeling {Interval Dates}

    Josep Heredia

      Hello everybody,

       

      I've a problem building a data model for HR APP.

       

      - I have 2 different tables of  measures, each table is connected to the other by the PERSONAL_ID and date FROM date TO

       

      Example:

       

      Table 1 : PERSONAL_DATA

      PERSONAL_IDFROMTOADRESS
      00000012016010120160131A
      00000012016020199991231B

       

      Table 2: CONTRACT

      PERSONAL_IDFROMTOCONTRACT_ID
      00000012016010120160115A
      00000012016011620160229B
      00000012016030199991231C

       

      What i need is to be able to use a CALENDAR table, that if I select DATE: 20160116 and the result be like:

       

      PERSONAL_IDADRESSCONTRACT
      00001AB

       

      And if i select whole January:

       

      PERSONAL_IDADRESSCONTRACT
      000001AA
      000001AB

       

      I tried to build a LINK table between the Calendar and the 2 tables and i doesn't work properly

      I tried to concatenate both tables and link the calendar by a link table ( Datelink, Date) and it doesn't either.

       

      How you would build the data modeling without increasing the 2 tables registers?