Interval Match to Multiple Tables

[k_burata]

Hi All,

Been struggling with this for a while. Below is a sample data model that I have:

question is: How do I do IntervalMatch the PositionHistory, Department, and Org Tables to a Calendar so that when I select a certain Month-Year it will show all the valid employees within the selcted date.

Cheers.

[Gysbert_Wassenaar]

I don't see why you need to match on multiple tables. Only PositionHistory makes sense to me. Something like this perhaps:

Calendar:

LOAD

     Date(MinDate + IterNo()) as Date

WHILE

     MinDate + IterNo() <= MaxDate;

LOAD 

     Date(min(PosStart)) as MinDate,

     Date(max(PosEnd)) as MaxDate

RESIDENT

     PositionHistory;

IntervalMatchTable:

IntervalMatch(Date)

LOAD

     PosStart,

     PosEnd

RESIDENT

     PositionHistory;

[k_burata]

Hi Gysbert,

Thanks for the reply. An employee might be moved to a different department so I have to trace what department he is in at a certain date.

Is it better if I include the department on the PositionHistory Table by adding a Dept field? But I have to match again the PosStart/PosEnd with DepStart/DepEnd.

[Gysbert_Wassenaar · Accepted solution]

The Dep field is part of the PositionHistory table. I assume (and hope for you) that if a person changes from department then a new record is added to the PositionHistory table with a new PosStart value and the old record is updated with the PosEnd value. Destructively updating the Dep field will make it impossible to track changes of department. So the information you want should already be stored in the PositionHistory table.

[k_burata]

Makes sense. Cheers mate.