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

    Create a join with IntervalMatch and a key

    Willy GARNIER



      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



      , 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:




        CUDIVI as %DIVI,

        CUCUCD as Code_devise,

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

        CUARAT as Taux_devise

      FROM CCURRA.QVD (qvd)









        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







        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








      INNER JOIN (Debut)


      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,