Create a join with IntervalMatch and a key

[#1]

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

[Gysbert_Wassenaar]

It's not clear to me what you're trying to do. But perhaps you can use the extended syntax of the intervalmatch function. It has its own entry in the help file IntervalMatch (Extended Syntax).