1 Reply Latest reply: Mar 17, 2015 9:29 AM by Gysbert Wassenaar RSS

    Create a join with IntervalMatch and a key

    Willy GARNIER

      Hello,

       

      I am trying to do the same thing as this, but with a script:

       

      Create table #table(DIVI varchar(3), Code_devise varchar(3), DateValidite_Devise datetime, Taux_devise decimal(15,5))

       

       

      INSERT into #table

      Select '1Z0', 'USD', '2015-01-01 00:00:00.000', 1.30

      UNION ALL Select '1Z0', 'USD', '2015-02-01 00:00:00.000', 1.20

      UNION ALL Select '1Z0', 'USD', '2015-03-01 00:00:00.000', 1.10

      UNION ALL Select '1Z0', 'USD', '2015-04-01 00:00:00.000', 1.00

      UNION ALL Select '1Z0', 'GBP', '2015-01-01 00:00:00.000', 0.9

      UNION ALL Select '1Z0', 'GBP', '2015-03-01 00:00:00.000', 0.95

      UNION ALL Select 'AAA', 'USD', '2015-01-01 00:00:00.000', 1.00

       

       

      Select DIVI

      ,Code_devise

      ,Taux_devise

      , DateValidite_Devise as StartDate

      , (

        SELECT ISNULL(MIN(EndTable.DateValidite_Devise),'2099-01-01 00:00:00.000')

        from #table EndTable

        WHERE StartTable.DIVI = EndTable.DIVI

        AND StartTable.Code_devise = EndTable.Code_devise

        AND StartTable.DateValidite_Devise < EndTable.DateValidite_Devise

        ) AS EndDate

      from #table StartTable

      --WHERE StartTable.DateValidite_Devise = '2015-03-01 00:00:00.000'

       

       

      DROP table #table

      So I have a table with a starting date, but no end date.

      The end date is the next period starting date...

      I am trying create a table with both start and end date.

       

      In my script I have:

       

      devises:

      LOAD

        CUDIVI as %DIVI,

        CUCUCD as Code_devise,

        date#(CUCUTD,'YYYYMMDD') as DateValidité_Devise,

        CUARAT as Taux_devise

      FROM CCURRA.QVD (qvd)

      WHERE

        CUCRTP=4

      ;

       

       

          

      Fin:

      LOAD

        Text(%DIVI)&Code_devise as clef,

        %DIVI as %DIVIFin,

        Code_devise as Code_deviseFin,

        DateValidité_Devise as DateValidité_DeviseFin,

        Taux_devise as Taux_deviseFin

      Resident devises

      ;

       

       

       

      Debut:

      LOAD

        Text(%DIVI)&Code_devise as clef,

        %DIVI as %DIVIDebut,

        Code_devise as Code_deviseDebut,

        DateValidité_Devise as DateValidité_DeviseDebut,

        Taux_devise as Taux_deviseDebut

      Resident devises

      ;

       

       

       

       

       

      TmpLien:

      INNER JOIN (Debut)

      IntervalMatch(DateValidité_DeviseDebut)

      LOAD 0, DateValidité_DeviseFin

      Resident Fin

      WHERE clef = '1Z0CHF'

      AND DateValidité_DeviseDebut = DateValidité_DeviseFin

      ;

       

      My problem is that I can join on:

      - the same name between the 2 tables

      - using IntervalMatch

       

      But not both in, the same time.

       

      Could you help me or give me another way of doing that ?

       

      Thank you,

      Willy