Hi,
My first time posting here so sorry for any mistakes.
I have two tables, one factual with employee movements and their countries on each date and another one with employees' assignments.
Table 1 - movements
| Date | Employee | Movement | Country |
| 01/01/2012 | A | Hire | Canada |
| 25/12/2015 | A | Transfer | US |
| 01/01/2018 | A | Termination | US |
| 25/01/2015 | B | Hire | Canada |
| 03/02/2018 | B | Transfer | Norway |
| 04/07/2016 | C | Transfer | Belgium |
| 01/01/2015 | D | Transfer | Norway |
Table 2 - assignments
| Employee | Assignment start date | Assignment end date | Coutry of assignment |
| A | 01/01/2013 | 01/12/2015 | US |
| B | 01/01/2016 | 01/01/2018 | Norway |
| C | 01/01/2016 | 01/07/2016 | US |
| D | 01/01/2016 | 01/01/2017 | Norway |
I want to count how many employees were transferred to their countries of assignment after the assignment completed. So, in this case, both employees A and B should be counted:
- employee A was transferred in 25/12/2015 from Canada to US and his country of assignment was US
- employee B was transferred in 03/02/2018 from Canada to Norway and his country os assignment was Norway
- employee C was transferred to Belgium after the end of assignment of in US, so should be disregarded.
- employee D was transferred before the assignment started, so should be also disregarded.
I am kind of stuck here and tried several ways to filter both columns (end date and countries). Any hints?
Tks in advance!
