I have a main data table (Onderdelen) and a table (PPCO) like below.
I would like to add a field to Onderdelen named OND_JUR_PPCO and fill it with a 1 when the orderdate (OND_D_BESTEL) from Onderdelen lies between PPCO_INGANGSDATUM and PPCO_EINDDATUM from the PPCO table. The 2 tables share a key as shown below. Per matching key there can be more than one record in the PPCO table and therefore the daterange is neccesary to find the right record.
So If the keys from both tables match and the orderdate lies between the date range then add OND_JUR_PPCO to Onderdelen with 1 or else with 0.
PPCO_OPDRGEVER & PPCO_MERKCODE & PPCO_SHB & PPCO_LEVERANCIER AS PPCO_KEY,
I can add both of the dates from PPCO with a left join into the Onderdelen Table but then I have to reload this huge table again to perform the date request. There must be an easier way.
do you need the PPCO table (that is the huge one, is it?) as such or is it only the start- and enddate that you need?
If it's the former and you do need the table as such - well, still loading RESIDENT from it just two fields should not pose an issue? You can join those to the other table and you can build an IF_construction to generate one new field.
Another, probably the more elegant solution, would be using IntervalMatch() in a LOAD statement - basically, with that you can find out if a date lies in the range of a start- and an end-date (among other possibilities).