Interval Match Problems

[anuradhaa]

I have two tables and want to use interval match to map records,

Table 1 : (Base table)

Name     Start     End

aaa     2015/03/14     2015/05/25

aaa     2016/01/12     2016/05/20

ABC     2016/02/12     2016/04/30

Table2

Name          Date

aaa               2016/02/14

aaa               2016/03/12

aaa                2015/04/14

I want to put largest date of each cycle (Start -> End) is date falls in date range Start - > End

Final Table

Name     Start     End      Date

aaa     2015/03/14     2015/05/25     2015/04/14

aaa     2016/01/12     2016/05/20     2016/03/12

ABC     2016/02/12     2016/04/30     -

[anuradhaa]

Table:

LOAD * Inline [

Name, Start, End ,country , flag

aaa, 2015/03/14, 2015/05/25 , US , Y

aaa, 2016/01/12, 2016/05/20 ,UK , N

ABC, 2016/02/12, 2016/04/30 , US , N

];

Final Table

Name     Start     End      Date country flag

aaa     2015/03/14     2015/05/25     2015/04/14 US Y

aaa     2016/01/12     2016/05/20     2016/03/12 UK N

ABC     2016/02/12     2016/04/30     - US N

[sunny_talwar · Accepted solution]

Check out attached, you don't have to add all the names and it should still work for you