Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
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.
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.
Makes sense. Cheers mate.