Delete or Merge Candidate ID

Owned by Robert Adach, created
Last updated: Apr 20, 2022
6 min read

The are occasions when two candidate ids are created for the same person. Normally, this issue is immediately identified and the duplicate record can be deleted, however, there are times when the duplicate record exists for a period of time and can not be immediately deleted as each record is associated to a different process (exam approval, MASS Preapp, or Registry entry).

For example:

  1. MSERV creates a Registry application for a individual and the system assigns Candidate ID 123456.

  2. When the registry record was created, there was no SSN provided or it was entered incorrectly.

  3. CSC later receives an exam approval from Florida for the same individual with a SSN

  4. Since the ID number does not match between the record from step 1 and this approval, CSC creates a new Candidate record (123457) and imports the approval

Prerequisites

  1. The Candidate ID that is to be deleted

  2. The Candidate ID that is to be retained (only needed if candidate id can not be deleted

  3. Access to the ASWBSQL2\ASWBSQL2\ASWB database (ASWB DB)

  4. Access to the 192.168.100.160\LAP database (LAP DB)

 Instructions

Delete the Candidate ID

  1. Run the following query on the LAP DB, after replacing the <CandidateID> value with the Candidate ID provided to you in the Jira ticket

  2.  

    DELETE FROM tblCandidate WHERE CandidateID = <CandidateID>

  3. If there are no records returned, then proceed to the next step. Otherwise, go to the “request candidate ID to merge” document

  4. Run the following query on the ASWB DB

  5. If the candidate ID is successfully deleted, you may respond to the Jira ticket that the CandidateID was removed

  6. If the Candidate ID was not successfully deleted,

    1. Identify the linked record (the table key error will tell you if it is linked to a Registry or MA Preapp)

    2. Go to the “Request candidate ID to merge” section of this document

Request candidate ID to merge

If the Candidate ID was found in the LAP database, then use this response in your Jira ticket

We are unable to delete the Candidate ID <CandidateID> as this ID is linked to a Massachusetts application. If there is another Candidate ID that the provided ID should be merged into, please provide it, otherwise we will close this ticket.

If the Candidate ID was not found in the LAP database, but could not be deleted, the use this response in your Jira ticket:

We are unable to delete the Candidate ID <CandidateID> as this ID is linked to <a Massachusetts application/a Registry Member>. If there is another Candidate ID that the provided ID should be merged into, please provide it, otherwise we will close this ticket.

Merging Candidate Records

  1. If the requester provides a Candidate ID to merge with

  2. Run the following script after updating the variables on lines 3 <CandidateID to keep> & 4 <CandidateID to delete>

  3. IF OBJECT_ID('tempdb..#SwapTable') IS NOT NULL DROP TABLE #SwapTable GO DECLARE @OrigCID INT = <CandidateID to keep> -- THE CID that we will be keeping DECLARE @DeleteCID INT = <CandidateID to delete> -- The CID that we will be deleting DECLARE @Continue INT = 0 -- Only change to one after validating information DECLARE @msg varchar(2000) = null --Variables used by the script DO NOT ALTER-- CREATE TABLE #SwapTable ( ActionToTake VARCHAR(6) , CID INT , Approvals INT , MAPreApp INT , RegCandidateAdd INT , RegSupervision INT , RegReferences INT , RegOtherDocumentation INT , RegLicensure INT , RegExperience INT , RegEducation INT , RegContinuingEd INT , Orders INT , FKPersonalNo_CandidateID INT , Exam INT , RegFormTracking INT ) --------------------------------------------- IF (@Continue = 0) BEGIN INSERT INTO #SwapTable (ActionToTake,CID, Approvals) SELECT CASE WHEN c.CandidateID = @OrigCID THEN 'Keep' ELSE 'Delete' END, c.CandidateID, COUNT(e.ACTNO) FROM tblCandidate c LEFT JOIN tblExam e ON e.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID UPDATE s SET MAPreApp = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblMAPreApp mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegCandidateAdd = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegCandidateAdd mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegSupervision = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegSupervision mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegReferences = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegReference mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegOtherDocumentation = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegOtherDocumentation mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegLicensure = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegLicensure mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegExperience = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegExperience mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegEducation = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegEducation mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegContinuingEd = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblRegContinuingEd mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET Orders = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblOrder mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET FKPersonalNo_CandidateID = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblCandidatePersonalNo mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET Exam = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.CandidateID) FROM tblCandidate c LEFT JOIN tblExam mra ON mra.CandidateID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID UPDATE s SET RegFormTracking = ISNULL(ma.[Count],0) FROM #SwapTable s INNER JOIN (SELECT c.CandidateID, [Count] = COUNT(mra.OwnerID) FROM tblCandidate c LEFT JOIN tblRegTracking mra ON mra.OwnerID = c.CandidateID WHERE c.CandidateID IN (@OrigCID,@DeleteCID) GROUP BY c.CandidateID ) as ma ON ma.CandidateID = s.CID SELECT * FROM #SwapTable END IF @Continue = 1 BEGIN BEGIN TRY PRINT FORMATMESSAGE ('Updating tblExam from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblExam SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblMAPreApp from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblMAPreApp SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegCandidateAdd from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegCandidateAdd SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegSupervision from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegSupervision SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegReferences from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegReference SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegOtherDocumentation from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegOtherDocumentation SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegLicensure from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegLicensure SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegExperience from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegExperience SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegEducation from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegEducation SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegContinuingEd from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegContinuingEd SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblOrderDetail from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblOrder SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblOrderDetail from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE od SET ACTNo = REPLACE(ACTNo,@DeleteCID,@OrigCID) FROM tblOrder o INNER JOIN tblOrderDetail od ON od.OrderID = o.OrderID WHERE CandidateID = @OrigCID PRINT FORMATMESSAGE ('Updating tblCandidatePersonalNo from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblCandidatePersonalNo SET CandidateID = @OrigCID WHERE CandidateID = @DeleteCID PRINT FORMATMESSAGE ('Updating tblRegFormTracking from CandidateID :: %d to %d',@DeleteCID,@OrigCID) UPDATE tblRegTracking SET OwnerID = @OrigCID WHERE OwnerID = @DeleteCID PRINT FORMATMESSAGE ('Deleting tblCandidate record for CandidateID :: %d ',@DeleteCID) DELETE FROM tblCandidate WHERE CandidateID = @DeleteCID END TRY BEGIN CATCH SET @msg = ERROR_MESSAge() END CATCH IF NOT((@msg = NULL OR @msg = '')) PRINT FORMATMESSAGE('We have sucessfully merged Candidate ID [%d] into Candidate ID [%d] as requested.',@DeleteCID,@OrigCID) ELSE BEGIN PRINT FORMATMESSAGE('Unable to merge Candidate ID [%d] into Candidate ID [%d]',@DeleteCID,@OrigCID) PRINT FORMATMESSAGE('DUE TO [%s]', @msg) END END

  4. Verify that 2 records were returned

  5. Update the @Continue variable (on line 6) in the script to “1” and rerun the script

  6. Update the @Continue variable (on line 6) in the script to “0” and run the script

  7. There should now be only 1 record and respond to the JIra ticket with the the last line in the output (We have successfully merged…)

 Related articles