Forum Discussion
Parent-Child relationship problem
Hello Guys,
I have a problem I've been trying to tackle but to no avail.
Problem:
An employee can be transferred to a different supervisor in the middle of the month.
| 5/1 - 5/15 | 5/16 - 5/31 | |||
| Employee A | Supervisor A | Employee A | Supervisor B | |
| Employee B | Supervisor A | Employee B | Supervisor B | |
| Employee C | Supervisor A | Employee C | Supervisor B |
In the table above, Employees A,B,C were assigned to Supervisor A from 5/1 - 5/15. All their stats should be retained under Supervisor A. From 5/16 - 5/31, they were assigned to Supervisor B. Likewise, all their stats from then on should fall under Supervisor B.
I can't seem to find the solution to this. Right now, I have the relationships set up like so:
| dim_Roster | dim_Calendar | |
| EmpID | Date | |
| Name | ||
| Supervisor | ||
| fact_Data | ||
| EmpID | ||
| Date |
The way I have it set up right now does not take into account supervisor changes within the month. I've looked into many-to-many relationships using a bridge table but still can't get it to work.
Appreciate any assistance. Thank you.
7 Replies
ambient_88,
Please share sample data of the above three tables and post expected result based on the sample data here.
Regards,
LydiaHello,
Please see the sample data in the link below:
https://drive.google.com/open?id=1w9h1X_irjQJAlCNJyDvDgLqO9zcZGZwn
Thanks is advance!
Hi,
I think i can help here. However, i notice that there is no Year in the data range of column D. Do you not get the year from the source?
Hello,
I should be able to get the year date for the column D.
Thanks!
