Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
You can use IntervalMatch with a keyfield (SITE_ID) . It would look something like the sample script below.
SET DateFormat='DD/MM/YYYY'; //Ensuring date format identical to how it is in the source table.
TT:
LOAD * inline [
TT_ID, TT_START_DATE, TT_END_DATE, SITE_ID,
1, 01/07/2024, 10/07/2024, A,
1, 01/07/2024, 10/07/2024, B
1, 01/07/2024, 10/07/2024, C
2, 05/07/2024, 15/07/2024, A
2, 05/07/2024, 15/07/2024, D
];
TSR:
LOAD * inline [
FCT_DATE, SITE_ID, DWH_CALL_ID
28/06/2024, A, 123
01/07/2024, A, 234
05/07/2024, A, 345
04/07/2027, D, 456
02/07/2024, B, 567];
LEFT JOIN (TSR)
IntervalMatch (FCT_DATE, SITE_ID)
LOAD TT_START_DATE, TT_END_DATE, SITE_ID
RESIDENT TT;
LEFT JOIN (TT) LOAD * RESIDENT TSR;
DROP TABLE TSR;
(Note that the DWH_CALL_ID = 123 will get lost as it is not within a defined interval.)
