Hi,
I have a table that looks something like below:
EmpID, LocationID,PositionID,StartDate,EndDate
1, A, 3, 1/23/2022, 3/5/2022
1, B, 3, 3/5/2022, -
2, A, 3, 7/14/2022, 12/01/2022
2, A, 1, 12/01/2022, 12/30/2022
3, A, 3, 4/17/2022, 6/03/2022
3, A, 3, 6/03/2022, -
I am trying to count distinct Employee IDs (EmpID) that have a valid EndDate in a given month, e.g.:
- Employee 1 has a valid EndDate in March 2022 (as the Employee changes location), so the Employee should be counted in March 2022.
- Employee 2 has two valid end dates in December 2022 as the Employee changes positions on 12/01/2022 and then leaves the company on 12/30/2022. Employee 2 should be counted only once for December 2022.
- Employee 3 does NOT have a valid EndDate. The user was EndDated on 6/03/2022 but then re-started on the same day with the same Location and Position. If the Employee has an EndDate and a StartDate on the same day where neither the Position or the Location changes, then they should not be counted for that month. Employee 3 should NOT be counted in June 2022.
Assume Employees can only hold one Position at a time and can be located at only one Location at at time.
Does anyone have ideas on how to approach this problem?
