join Intermatch 2 dates

[anwarbham]

Hi im having difficulty joining 2 tables based on start date and end date

i have 2 tables 

the Account Table that has a unique consent ID where the key is Account_Key 

and the Cycle Table where there is  also Account_Key
both tables have a start date and end date. or capture date and expiration date

i need to find out which Consent IDs from the Account Table , can be linked to which cycles

i can use a join Account -Account and then the capture date with intermatch to start date and end date. 

but i also want to include the records that fall  before the  expiration date, how can i achieve this 

[edwin · Accepted solution]

on high level, you need to relate the two tables row by row where the AccountKey are the same and the dates overlap.  to do this:
1. create a link table which is an outer join of the two tables on AccountKey; include keys for each table + the dates
2. then retain only the rows where the dates overlap
3. drop the AccountKey and the dates as you only need the keys to link back to the two tables.

Obviously, AccountKey needs to have different names in the original tables.   the relationship will look like this:

here is the sample code:

table1: load %consentKey, %AccountKey, date(today()+offset1) as capDate, date(today()+offset2) as expDate inline [
%consentKey, %AccountKey,offset1, offset2
z0001, 1, -5, 5
z0002, 2, -2, 4
];

NoConcatenate table2: load %cycleKey, %AccountKey2, date(today()+offset1) as startDate, date(today()+offset2) as endDate inline [
%cycleKey, %AccountKey2, offset1, offset2
x0001, 1, -2, 3
x0002, 1, 6, 9
x0003, 2, -10, 1
x0004, 2, -10, -9
];

NoConcatenate
bridge1: load distinct %AccountKey, %consentKey, capDate as capDate2, expDate as expDate2 resident table1; 

inner join (bridge1)
load distinct %AccountKey2 as %AccountKey, %cycleKey, startDate as startDate2, endDate as endDate2 resident table2; 

NoConcatenate
bridge: load %consentKey, %cycleKey Resident bridge1
where capDate2>=startDate2 and expDate2 >=endDate2  and endDate2>=capDate2 or
capDate2<=startDate2 and expDate2 <=endDate2 and expDate2>=startDate2 or
capDate2 >=startDate2 and endDate2 >=expDate2 or
startDate2 >=capDate2 and expDate2>=endDate2;

drop table bridge1;

so only the ones that overlap will be associated:

disclaimer: not sure if ive captured all the test cases for the overlapping dates - you need to add on to the where clause if there are other test cases.