Connecting two tables and put new key into second table

[chrisk44]

Hello again, 

I want connect two tables by adding key to  second table.

Source tables look like that:

TAB1

KEY	ID	DATE_FROM	DATE_TO
11311	1131	1970-08-19	2007-12-31
11312	1131	2008-01-07	2008-05-10
23851	2385	1996-01-02	2006-12-31
23852	2385	2008-07-16	2010-01-31
23853	2385	2018-11-30

TAB2

KEY2	ID	DATE_FROM	DATE_TO
1182495	1131	1973-02-16	1973-12-15
1182497	1131	1997-06-30	1997-06-30
1182515	1131	2000-09-05	2000-09-05
1301766	1131	2008-04-11	2008-04-11
1301849	1131	2008-05-05	2008-05-06
1302872	1131	2008-05-07	2008-05-09
841388	2385	2004-07-19	2004-07-19
1202499	2385	1997-01-02	1997-01-03
1202518	2385	1998-01-02	1998-01-02
1339364	2385	2009-10-12	2009-10-31
1339365	2385	2009-11-01	2009-11-27
1344561	2385	2010-01-04	2010-01-06

I need put value of key column to TAB2

When ID from TAB2 is EQ ID TAB1 and DATE_FROM and DATE_TO in TAB2 are between DATE_FROM and DATE_TO in TAB1, i put KEY into TAB2. Destination TAB2 should look like this:

KEY2	KEY	ID	DATE_FROM	DATE_TO
1182495	11311	1131	1973-02-16	1973-12-15
1182497	11311	1131	1997-06-30	1997-06-30
1182515	11311	1131	2000-09-05	2000-09-05
1301766	11312	1131	2008-04-11	2008-04-11
1301849	11312	1131	2008-05-05	2008-05-06
1302872	11312	1131	2008-05-07	2008-05-09
841388	23851	2385	2004-07-19	2004-07-19
1202499	23851	2385	1997-01-02	1997-01-03
1202518	23851	2385	1998-01-02	1998-01-02
1339364	23852	2385	2009-10-12	2009-10-31
1339365	23852	2385	2009-11-01	2009-11-27
1344561	23852	2385	2010-01-04	2010-01-06

[amrinder · Accepted solution]

Hi,

Try below script:

T1:LOAD * Inline
[
KEY,ID,DATE_FROM,DATE_TO
11311,1131,1970-08-19,2007-12-31
11312,1131,2008-01-07,2008-05-10
23851,2385,1996-01-02,2006-12-31
23852,2385,2008-07-16,2010-01-31
23853,2385,2018-11-30,
];

T2:LOAD * Inline
[
KEY2,ID,DATE_FROM,DATE_TO
1182495,1131,1973-02-16,1973-12-15
1182497,1131,1997-06-30,1997-06-30
1182515,1131,2000-09-05,2000-09-05
1301766,1131,2008-04-11,2008-04-11
1301849,1131,2008-05-05,2008-05-06
1302872,1131,2008-05-07,2008-05-09
841388,2385,2004-07-19,2004-07-19
1202499,2385,1997-01-02,1997-01-03
1202518,2385,1998-01-02,1998-01-02
1339364,2385,2009-10-12,2009-10-31
1339365,2385,2009-11-01,2009-11-27
1344561,2385,2010-01-04,2010-01-06
];

T3:LOAD
KEY,
ID,
Date(Date#(DATE_FROM,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_FROM1,
Date(Date#(DATE_TO,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_TO1
Resident T1;

Inner Join

LOAD

KEY2,
ID,
Date(Date#(DATE_FROM,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_FROM2,
Date(Date#(DATE_TO,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_TO2
Resident T2;

NoConcatenate
LOAD
KEY2,
ID,
DATE_FROM2,
DATE_TO2

Resident T3 Where DATE_FROM2>=DATE_FROM1 and DATE_TO2<=DATE_TO1;

DROP Tables T1,T2,T3;

[chrisk44]

Thank You for reply - need some time to check solution meanwhile got another problem... but this in another thread...
Regards

[chrisk44]

Everything works great (with small correction):

NoConcatenate
LOAD
KEY,
ID,
KEY2,
DATE_FROM2,
DATE_TO2

Great WORK !!!