2 Replies Latest reply: Apr 11, 2013 2:12 PM by Yusuf Ali RSS

    MATCHING 2 TABLES Problem

      Hi to All!!

      I have a problem

      I have 2 tables and i have to join those tables but, as follow:

       

      TABLE A

      ID       ACD         DATE

      1           A            1/1/2013

      2           B          10/1/2013

       

       

      TABLE B

      ACD          DATESTART       DATEEND       MODEL

      A               1/12/2012          31/12/2012          A1

      A               1/1/2013            31/12/2013          A2

      B               1/1/2013            31/12/2013          B1

      B               1/1/2014            31/12/2014          B2

       

       

      RESULT of JOIN

      TABLE C

      ID          ACD          MODEL

      1               A               A2

      2               B               B1

       

       

      As you can see, the join is based on ACD as Key, and the comparison of DATE from TABLE A against DATESTART and DATEEND from TABLE B

       

      How can i perform this???

      Thanks!!!

        • Re: MATCHING 2 TABLES Problem
          Marco Raymundo

          Try

           

          Table A;

           

          Right Join (Table A)

          LOAD

          FROM Table B;

          • Re: MATCHING 2 TABLES Problem
            Yusuf Ali

            Hi ,

                 Please try the below code .

            TABLEA:

            ID       ACD         DATE

            1           A            1/1/2013

            2           B          10/1/2013

             

             

            TABLEB:

            ACD          DATESTART       DATEEND       MODEL

            A               1/12/2012          31/12/2012          A1

            A               1/1/2013            31/12/2013          A2

            B               1/1/2013            31/12/2013          B1

            B               1/1/2014            31/12/2014          B2

             

            NoConcatenate

            TABLEC:

              LOAD ID ,ACD , DATE

            Resident TABLEA;

            Left Join

            IntervalMatch(DATE)

              LOAD ACD,          DATESTART,       DATEEND,       MODEL

            Resident TABLEB;

             

             

            DROP Table TABLEA,TABLEB;

             

            If there is anything please let me know