Load employee presence dates table based on interval

[DutchArjo]

I want to load an employees presence table with all the dates somebody was present or not based on tasks he or she was assigned to. I have a separate table which has all the tasks and their start and end date.

I know Intervalmatch should do some trick for me in this but I do not succeed in thinking how I should get this.

and actually, it's a 2-step rocket: first, an employee is present or not for a range of dates based on the tasks the employee is assigned to. But secondly, it's possible for many reasons an employee is present although assigned for a tasks (e.g. illness, holiday, education etc.). These dates are also recorded in a separate table.

How should I do this?

Below is (a port of) the model I have now:

the activities(tasks) are recorded in the table 'ACTIVITEITEN'.

(activity id, activity start date, activity end date).

'MDW_AANWEZIGHEID' now contains a employee/datekey (employeeID & date (yyyy-mm-dd).

That key checks in the AFWEZIGHEID table for a match for the employee/date key.

and MDW_ACTIVITEITEN contains the employee_ID and the tasks(activity_ID) he or she is assigned to.

From this, I should be able to get alle the dates he/she normally would be present am I?

CAL_Date in the table MasterCalendar contains all the dates matching with the ranges in the table 'ACTIVITEITEN'.

[HirisH_V7]

You can create a column called Workday intially based on set of tasks. It will be 1 if its working day else 0. Based on this you can do the calucations, it will give desired results. Even in interval match also you can use this.

[marcus_sommer]

The main-idea to use an intervalmatch-logic to resolve the date-ranges into dedicated dates is the right one but mostly it could be done with a simpler and more powerful approach of applying an internal load-loop per while-clause, like:

load *, ID & '|' & Date as KeyX;
load *, applymap('CalendarWorkingDays', Date, '#NV') as DayCounter;
load ID, date(From + iterno() - 1) as Date, PresenceState
from X while + iterno() - 1 >= To;

Especially if there is more as a single range to resolve and/or several resolve-views are needed (here maybe against the Employee and the Tasks) and/or the ranges contain some kind of overlapping (half day in presence + half day ill or working on several tasks on a single day) the classical intervalmatch becomes easily complicated.

That you have two presence tables is not really a challenge because they contain mainly the same information - just differentiating in a presence-state. Therefore they could be at first concatenated and then resolved with the while-loop whereby the state becomes a part of the table.