I am trying trying to solve for tracking employee metrics when there is movement within the organization. I have an employee roster file that is updated when someone moves (from one dept to another, changes supervisors, etc.) but it is only updated periodically when a change occurs. I have data points for every employee, every day and I need to ensure the data aggregates properly based on the employee assignment dates in the roster file when we roll-up to the department/region/supervisor level.
I'll try to use a football example to make this a little clearer (see attachment). I have a record of every QB in the NFL and the seasons they played with any given team (roster file). I have a separate table containing their game stats for every game (data file). I want a complete picture of all games played for every QB since 2000. How can I use the To and From dates in my roster file to make sure their game stats get assigned to the correct teams?
This is driving me nuts so any input or suggestions would be appreciated.
