Skip import of duplicate records (and merging) according to latest note. (#2)

Hi Experts,
I need the same solution provided in in following post
https://www.experts-exchange.com/questions/29097687/Skip-import-of-duplicate-records-and-merging-according-to-latest-note.html 
to apply in a similar scenario.

Table1
Employeestbl
ID (PK)
Email
SkipImport (int)

Instead of having Notes table, we have the following two tables.
Orienatations
ID (PK)
FacilityID (int)
EmployeeID (ID)

OrientationNotes
ID (PK)
OrientationID
Note
Day (Datetime)

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.OrientationID) 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.
LVL 4
bfuchsAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Three part process

1) Flag EmployeesTbl
2) Update OrientationNotes to point to the correct OrientationsID
3) Delete Orientations with Skipped Employee.ID

NEED TO BACKUP BEFORE YOU COMMIT UPDATES AND DELETES

At the end of each CTE the update/delete occurs. I have commented out the SELECT * FROM CTE_* if you want to see the cte before tring the actual update, simply remove the comment '--' prefix from the three lines after the closing bracket of the CTE.

-- Part 1
-- Find Employees with Dupe emails
-- and update those ID's to SkipImport

;with CTE_Emp as
( select row_number() over (partition by e.email order by o.[day] desc) as RN, E.ID as E_ID, E.Email, E.SkipImport,O.*
  from EmployeesTbl E
  outer apply (select top 1 o.id as O_ID,o.EmployeeID,o.FacilityID,n.id as n_ID,n.orientationid,n.[day],n.note from Orientations O inner join OrientationNotes N on N.orientationID = O.ID where O.EmployeeID = E.id order by [day] desc ) O --on O.EmployeeID = E.id
) --/*
  update E set skipimport = case when RN = 1 then 0 else 1 end 
  from Employeestbl E
  inner join CTE_Emp C on C.E_ID = E.ID 
  --*/
  --Select * from CTE_Emp 

select * from EmployeesTbl

-- Part 2   WARNING - BACKUP
-- Update the OrientationNotes table with the corrected OrientationID.
-- Backup before committing the updates

;with CTE_Notes as  
( select D.BAD_ID, D.NEW_ID, O.id O_ID, O.EmployeeID,o.FacilityID, N.*
  from Orientations O 
  Inner join OrientationNotes N on O.ID = N.OrientationID
  left outer join (select O.ID OLD_ID from Orientations O inner join EmployeesTbl E on O.EmployeeID = E.ID and E.SkipImport = 1) U on U.OLD_ID = O.id
  left outer join (select D.id BAD_ID,O.id NEW_ID, E.id Emp_ID, S.id SKIP_ID 
                   from Orientations D 
                   inner join EmployeesTbl S on D.EmployeeID = S.ID and S.SkipImport = 1
                   inner join EmployeesTbl E on E.Email = S.email and E.SkipImport = 0
                   inner join Orientations O on O.facilityID = D.facilityID and O.employeeID = E.id) D on D.BAD_ID = U.OLD_ID

) --/*
  update O set OrientationID = N.NEW_ID 
  from OrientationNotes O
  inner join CTE_Notes N on N.BAD_ID = O.OrientationID 
  where N.BAD_ID <> N.NEW_ID
  --*/
  --select * from CTE_Notes

  select * from OrientationNotes


-- Part 3   WARNING - DELETE REDUNDANT ORIENTATIONS - BACKUP FIRST
-- Clean up Orientations table with Skipped / replaced EmployeeID.
-- Backup before committing the deletes

;with CTE_Emp as  
( select O.id O_ID, O.EmployeeID,O.FacilityID,E.*
  from Orientations O 
  Inner join (select O.ID OLD_ID, E.* from Orientations O inner join EmployeesTbl E on O.EmployeeID = E.ID and E.SkipImport = 1) E on E.OLD_ID = O.id
) --/*
  delete from Orientations where ID in (select O_ID from CTE_Emp) 
  --*/
  --select * from CTE_Emp

select * from Orientations

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Isnt that a case of removing the old EmployeeID's that no longer exist in OrientationNotes (or Employeestbl.ID where skipImport = '1') ?

Because orientation Notes have been moved to the rectified / resolved / corrected Employeestbl.ID - I would have thought that entry would already be in Orienttions....

Or, is Notestbl from previous question a different table to OrientationNotes ?

And therefore a similar albeit entirely different set of tables ?

In which case, could there be a risk of multiple / different / conflicting skipimport flagging on employeestbl ?

edit : you did say :Instead of having Notes table, we have the following two tables. so there is no Notestbl ?
0
 
bfuchsAuthor Commented:
Hi Mark,
Or, is Notestbl from previous question a different table to OrientationNotes ?
Correct.
Thanks,
Ben
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
bfuchsAuthor Commented:
To clarify a bit.

The process is very similar, with two differences outlined above.

1- OrientationNotes are related to Orientations, which are related to Employees table.
2- The extra step #3 that needs to be done in case an orientation record exists already for that Emp/Fac.

Let me know if more clarification needed.

Thanks,
Ben
0
 
bfuchsAuthor Commented:
See attached.

Thanks,
Ben
Untitled.png
0
 
Mark WillsTopic AdvisorCommented:
Hmmmm....  A lot more *interesting*

1) It is possible to have a few Employeestbl.ID for an email
2) It is possible to have a few EmployeeID for different FacilityID
3) It is possible to have Notes for each of 2 above

So, it is possible to have different Employeestbl.ID with the same Email - one for faciityID 'n' and one for facilityID 'n+' both legitimately existing in orientations because it satisfies the unique index.

Is the objective still to have one unique Employeestbl.ID per email ? In which case, what will be the determining factor as to which Orientations.EmployeeID wins ?
0
 
bfuchsAuthor Commented:
Hi,
Is the objective still to have one unique Employeestbl.ID per email ? In which case, what will be the determining factor as to which Orientations.EmployeeID wins ?
Yes this is the case.

So basically we need to look at the OrientationNotes table which employee has the latest note (just the way you did in the other post for notes table, with one difference, here you have to join the Orientations table as well to determine to which employee this notes belongs..).

At the second step, we only need to update the Orientations table, to assign it to the main employee, once thats done, all related notes to that orientation should automatically be under that employee.

However while running the second step, we may anticipate an error saying not all records were successful, as it would create duplicate in unique index for EMP/FAC.

And therefore there must be a third step to handle those records.
So for example if we have 2 records in Orientations table
ID        FacilityID     EmployeeID
1                       100                      15
2                       100                      25
And we want combine those two employees 15 & 25 into one, (since they have same email)
Instead of changing the Orientation record from 15 to 25 (in case 25 has the latest note).
We change all records in OrientationNotes table that OrientationID is 1 to 2.

Does that make sense?

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
Yep, that makes sense - thanks
0
 
bfuchsAuthor Commented:
Hi Mark,

So can I count on you for this one as well?

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
Yep, will be tied up with dumb things I gotta do for a few hours, and will get stuck into it on my return...
0
 
bfuchsAuthor Commented:
Great!

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi Mark,

As my manager is eager to get this done ASAP.
Wondering when is your avail?
perhaps Par1 I can try copy from the other one, if that makes it easier/faster..

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi,
Three part process

1) Flag EmployeesTbl
2) Update OrientationNotes to point to the correct OrientationsID
3) Delete Orientations with Skipped Employee.ID
#1, agree.
Re #2 & #3, I'm not fully getting it, why are you performing an update to ALL OrientationNotes, as from my understanding for most of them it would be only necessary to update the Orientations table, and switch over the orientation to the right employee, this would automatically bring along all notes associated with them.
While for those that have already a Orientation in place with that facility, it would be necessary to update the OrientationNotes table, and determine which is the right Orientation to transfer the records to..(The one with EmployeeID = MainEmployee SkipImport = 0, and FacilityID = current orientation.facilityid)

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
1) it isnt for all orientationnotes, just those where the employeeid in orientations will be changing where an existing employeeid already exists in orientations for that facilityid.

2) it is possible that there is an orientationID pointing to an orientations.ID where the employeeID already exists and therefore that orientationID becomes redundant (it cannot be updated to the corrected employeeid - unique constraint prevents it) which means the orientationID is no longer relevant and has to point to the correct (existing) row.
0
 
bfuchsAuthor Commented:
Hi,
Got you!
Testing it..
Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi Mark,

Preliminary tests had showed its working, was waiting for users to confirm, however that doesn't look to be so fast, so I'm closing it.

Thank you very much!
Ben
0
 
bfuchsAuthor Commented:
Hi Mark,
Something is missing from this request.
However I will open a new one, let me know if you are now avail..?
btw, I also have a couple of these tasks
Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
>> btw, I also have a couple of these tasks

 :) out and about on site today, but do have some time available overnight + tomorrow  (my time GMT + 10:00)
0
All Courses

From novice to tech pro — start learning today.