Matching dates between different tables

[#1]

Hi everybody,

I am trying to match dates between two different tables.

I have one table with the following fields:

Inv_dates:

Part_Part,

Inv_date

and second table with :

Cost_cal_date:

Part ,

Cost_cal_date

The data in these tables is , as an example :

1:

23	1/1/2013
78	1/2/2013
78	25/02/2013
88	3/3/2013
88	5/3/2013

2:

23	31/12/2012
23	31/01/2013
23	28/02/2013
23	31/03/2013
23	30/04/2013
23	31/05/2013
78	31/12/2012
78	31/01/2013
78	28/02/2013
78	31/03/2013
78	30/04/2013
78	31/05/2013
88	31/12/2012
88	31/01/2013
88	28/02/2013
88	31/03/2013
88	30/04/2013
88	31/05/2013

What I am trying to do is to on the first table, for each line, the most close COST_CAL_DATE from the second table but which is earlier than the date on the line. for example

23	1/1/2013	31/12/2012
78	1/2/2013	31/01/2013
78	25/02/2013	31/01/2013
88	3/3/2013	28/02/2013
88	5/3/2013	28/02/2013

I have tried to first join both tables, with left join and than to read from the same table for a new one with

'where max(COST_CAL_DATE) <= INV_DATE' but it didn't work.

I also tried to work with the INTERVALMATCH function but it didn't work.

I will appreciate any help.

Thank You,

Osher Sassoni.

[settu_periasamy]

Hi,

Try the below script..

Inv:
LOAD * INLINE [
    Part, Inv_dates
    23, 1/1/2013
    78, 2/1/2013
    78, 2/25/2013
    88, 3/3/2013
    88, 3/5/2013
];
Left Join(Inv)
Cost:
LOAD * INLINE [
    Part, Cost_cal_date
    23, 12/31/2012
    23, 1/31/2013
    23, 2/28/2013
    23, 3/31/2013
    23, 4/30/2013
    23, 5/31/2013
    78, 12/31/2012
    78, 1/31/2013
    78, 2/28/2013
    78, 3/31/2013
    78, 4/30/2013
    78, 5/31/2013
    88, 12/31/2012
    88, 1/31/2013
    88, 2/28/2013
    88, 3/31/2013
    88, 4/30/2013
    88, 5/31/2013
];
NoConcatenate
New:
LOAD   Part, 
  Inv_dates,
  Max(Cost_cal_date) as Cost_cal_date 
Resident Inv Where Cost_cal_date<=Inv_dates Group by Part,Inv_dates;

DROP Table Inv;