I need the same solution provided in in following post
to apply in a similar scenario.
Instead of having Notes table, we have the following two tables.
So basically we need 3 tasks
1- Update Employeestbl set SkipImport = 0 where id in (select employeeID from orientations inner join OrientationNotes on Orientations.ID = OrientationNotes.Orientati
onID) however this needs to be modified to look for last note according to day field.
2- After Part1 is done, assign all Orientations from all employees with same email address into one employee (the one with SkipImport = 0).
However there is one but here..
Since there is a unique index in Orientations table for (EmployeeID, FacilityID), meaning an employee cant have 2 entries for same facility, in that case we need to move all notes from one orientation record to the other.
FYI- Employeestbl is related to Orientations one to many, Orientations is related to OrientationNotes one to many.
Thanks in advance.