2 Replies Latest reply: Dec 18, 2017 8:01 AM by Sergii Karandin RSS

    Mapping Codes to Field values based on timestamps

    Sergii Karandin

      Hello everyone, I'm trying to find a solution to the following problem and would appreciate any help

       

      There are two table:

      1) TransactionTable

      DateLoginAction
      01.02.2017Login1A1
      01.05.2017Login1A2
      01.09.2017Login1A3
      01.03.2017Login2A4
      01.08.2017Login2A5

      2) MappingTable

      LoginCodeInclusionDateExclusionDate
      Login1Code101.01.201720.03.2017
      Login1Code221.03.201715.08.2017
      Login1Code316.08.201729.11.2017
      Login2Code10001.01.201701.06.2017
      Login2Code10102.06.201729.11.2017

       

      I am trying to add Code field to the TransactionTable:

       

      DateCodeLoginAction
      01.02.2017Code1Login1A1
      01.05.2017Code2Login1A2
      01.09.2017Code3Login1A3
      01.03.2017Code100Login2A4
      01.08.2017Code101Login3A5

       

      Given that a Code for certain Login can change arbitrary number of times.

       

      I was thinking about creating a new table such as

      CodeDate
      Code101.01.2017
      Code102.01.2017
      Code103.01.2017
      etcetc

      and than map using where exists statement on dates, but I guess there can be more elegant and less resource-dependent solutions.


      Thanks in advance (and apologies if there already been such topic, I couldnt find any)