Interval Match

[rammuthiah]

I have one data to find the Interval match : Can anyone help in this regard?

Item	Odate	Crny	Cost
1	15/1/2012	EUR	14
2	15/2/2012	USD	15
3	15/3/2012	EUR	16
4	15/8/2012	USD	10

Another data

Crny	From	To	rent
USD	1/1/2012	30/4/2012	1.25
USD	1/6/2012	30/12/2012	1.29
EUR	1/1/2012	31/1/2012	1.23
EUR	2/2/2012	30/4/2012	2.25

The result should be

Item	ODate	Crny	From	To	Cost	rent
1	1/15/2012	EUR	1/1/2012	1/31/2012	14	1.23
2	2/15/2012	USD	1/1/2012	4/30/2012	15	1.25
3	3/15/2012	EUR	2/2/2012	4/30/2012	16	2.25
4	8/15/2012	USD	6/1/2012	12/30/2012	10	1.29

[sunny_talwar · Accepted solution]

Try this

Table1:

LOAD * INLINE [

    Item, Odate, Crny, Cost

    1, 15/1/2012, EUR, 14

    2, 15/2/2012, USD, 15

    3, 15/3/2012, EUR, 16

    4, 15/8/2012, USD, 10

];

Table2:

LOAD * INLINE [

    Crny, From, To, rent

    USD, 1/1/2012, 30/4/2012, 1.25

    USD, 1/6/2012, 30/12/2012, 1.29

    EUR, 1/1/2012, 31/1/2012, 1.23

    EUR, 2/2/2012, 30/4/2012, 2.25

];

Left Join (Table1)

IntervalMatch(Odate, Crny)

LOAD From,

  To,

  Crny

Resident Table2;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;