I guess that once an OrgLevel3 value changes, it will have one or more successive definitions for neuerName. The only thing you need to add to table Abteilungshistorie is start records that have no start date, an end date one day less than the first change record, and a neuerName equal to the original OrgLevel3. Then you can use INTERVALMATCH to get a connection between the source and history table (never mind the synthetic keys), and start using neuerName in all cases to obtain the correct OrdLevel3 name for that specific point in time. Right?
Just my 2cts.
you could create a gigantic mapping table where the index is composed of Original OrgLevel3 & '-' & Date and the lookup value is the new name for that moment in time.
You only have to create entries for each row in Abteilungshistorie, because the default correct original value OrgLevel3 can be passed as a third parameter to applymap() so that whenever a date isn't found, the original name will be returned.
Use a master calendar-like technique to translate Abteilungshistorie into a mapping table, e.g.
LOAD OrgLevel3 & '-' & date(BeginDate+IterNo()-1) as Index,
neuerName as LookupVal
WHILE BeginDate+IterNo()-1 <= EndDate;
Thank you so far Peter,
I would like to take solution A (no huge mapping table).
In my first posting I forgot to mention that the new name for OrgLevel3 depends on NEA_Datum from table NEA_Vorgänge. Is it still possible to find a smart solution?
It is possible that in future the Name of the department will change so I would have to add another period for an existing OrgLevel3. On the other hand there will be departments which never ever will change their names. Do I have to put up a line with these Names where the values for 'OrgLevel3' and 'neuerName' are the same?