How to acheive comparison using between and one or more other columns.

[#1]

I have two tables and would like to join on two columns with equality and other using between. similar to the one in SQL as below. (excuse me for any syntax errors). I tried with IntervalMatch which does not seems to work because of extra check required for ID column.

INSERT INTO FINAL_TABLE (FromDate,   ToDate,     ID, Value, Type)

SELECT FromDate,   ToDate,     ID, Value, Type

  FROM TABLE_A

UNION ALL

SELECT TABLE_B.FromDate,   NULL as ToDate,     TABLE_B.ID, TABLE_A.Value, TABLE_B.Type

  FROM TABLE_B

  LEFT JOIN TABLE_A ON TABLE_A.ID = TABLE_B.ID AND TABLE_B.FromDate BETWEEN TABLE_A.FromDate AND TABLE_A.ToDate

Table structures as following:

Table_A:

FromDate,   ToDate,     ID, Value, Type

01/01/2015, 01/30/2015, 1,  10,    TableA

01/01/2015, 01/30/2015, 2,  11,    TableA

02/01/2015, 02/15/2015, 2,  15,    TableA

Table_B:

FromDate,   ID, Type

01/15/2015, 1,  TableB

01/15/2015, 2,  TableB

02/15/2015, 2,  TableB

02/15/2015, 3,  TableB

Table to be created

Final_Table:

FromDate,        ToDate,          ID,      Value,      Type

01/01/2015,      01/30/2015,      1,       10,         TableA

01/01/2015,      01/30/2015,      2,       11,         TableA

02/01/2015,      02/15/2015,      2,       15,         TableA

01/15/2015,      NULL,           1,       10,           TableB

01/15/2015,      NULL,           2,       11,           TableB

02/15/2015,      NULL,           2,       15,           TableB

02/15/2015,      NULL,           3,       NULL,      TableB

[vardhancse]

Table_A:

FromDate,   ToDate,     ID, Value, Type

01/01/2015, 01/30/2015, 1,  10,    TableA

01/01/2015, 01/30/2015, 2,  11,    TableA

02/01/2015, 02/15/2015, 2,  15,    TableA

Left join(Table_A)

FromDate,   ID, Type

01/15/2015, 1,  TableB

01/15/2015, 2,  TableB

02/15/2015, 2,  TableB

02/15/2015, 3,  TableB

Based on FromDate both the tables will be joined

[#3]

Please observed the date values in the FromDate in TABLE_B they are not same as TABLE_A or in other words it is to be compared between TABLE_A FROM/TO Dates to find pick appropriate TABLE_A.Value.

[sunny_talwar · Accepted solution]

This:

Script:

Table_A:

LOAD * Inline [

FromDate,  ToDate,    ID, Value, Type

01/01/2015, 01/30/2015, 1,  10,    TableA

01/01/2015, 01/30/2015, 2,  11,    TableA

02/01/2015, 02/15/2015, 2,  15,    TableA

];

Table_B:

LOAD FromDate as Date,

  ID,

  Type as Type2

Inline [

FromDate,  ID, Type

01/15/2015, 1,  TableB

01/15/2015, 2,  TableB

02/15/2015, 2,  TableB

02/15/2015, 3,  TableB

];

Join(Table_B)

IntervalMatch:

IntervalMatch (Date, ID)

LOAD FromDate,

  ToDate,

  ID

Resident Table_A;

NewTable:

NoConcatenate

LOAD FromDate,

  ToDate,

  ID,

  Value

Resident Table_A;

Join(NewTable)

LOAD FromDate,

  ToDate,

  ID,

  Date,

  Type2

Resident Table_B;

Concatenate(Table_A)

LOAD Date as FromDate,

  Null() as ToDate,

  ID,

  Type2 as Type,

  Value

Resident NewTable;

DROP Tables NewTable, Table_B;

[#5]

Sunny that was excellent,That was exactly what i wanted. I was not aware IntervalMatch() would take multiple parameters.

[sunny_talwar]

It does . I recently figured that out also.

I am glad we are learning something new each day.

Best,

Sunny