1 of 2: Individual update
1. Script to update a physician to retired
2. Add your first name initial and last name for @UserName and your user key for @UserKey
3. Check that the physician page looks as it should with 'Retired' in red under name and inactive certs
USE WEBCAD
DECLARE @PersonKey AS INT
DECLARE @EffectiveDate AS DATETIME
DECLARE @UserName AS VARCHAR(20)SET @PersonKey = 144118
DECLARE @UserKey AS INT
SET @EffectiveDate = GETDATE()
SET @UserName = 'JVollmer';PRINT 'Updating Certificates'
SET @UserKey = 188
UPDATE [Certificate]
SET CertificateStatus = 2, Diplomate_History_Status_Key = 8, CCStatusid = NULL, ExpirationDate = @EffectiveDate, UpdatedBy = @UserName, UpdatedDate = GETDATE()
WHERE Person_Key = @PersonKey AND CertificateStatus = 1
INSERT INTO Diplomate_History (Diplomate_Key, Candidacy_Key, Diplomate_History_Status_Key, Diplomate_History_Status_Date, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, Diplomate_History_Expiration_Date, Note,
Date_Added, Date_Updated, User_Key)
SELECT Diplomate_History.Diplomate_Key, Candidacy_Key, 8, @EffectiveDate, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, @Effectivedate, 'Updated physician to certified-retired',
GETDATE(), GETDATE(), User_Key
FROM Diplomate_History
INNER JOIN (SELECT Diplomate_History.Diplomate_Key, MAX(Diplomate_History.Diplomate_History_Key) AS MaxDipKey FROM Diplomate
INNER JOIN Diplomate_History ON Diplomate_History.Diplomate_Key = Diplomate.Diplomate_Key
WHERE Diplomate.Person_Key = @PersonKey
GROUP BY Diplomate_History.Diplomate_Key) AS MaxDip ON MaxDip.MaxDipKey = Diplomate_History.Diplomate_History_Key
WHERE Diplomate_History.Diplomate_History_Expiration_Date > GETDATE()
--if there are any cc certificates being expired, end the cc certificate enrollment
UPDATE [CCEnrollmentCertificate] SET EnrollmentEndDate = @EffectiveDate, UpdatedBy = @UserName, UpdatedDate = GETDATE()
WHERE CertificateId in @CertificateId
UPDATE Person
SET CurrentCCEnrollmentId = NULL,
Note = CONCAT(Note, '; 3/18/2024 Updated to Retired by IT'),
UpdatedBy = @UserName, UpdatedDate = @EffectiveDate
WHERE Person_Key = @PersonKey;
UPDATE Diplomate
SET Clinical_Status_Id = 3, Date_Updated = @EffectiveDate, User_key = @UserKey
WHERE Person_Key = @PersonKey;
---------------------------------------------------------------------------------------------------------------------------------------
2 of 2: Multiple update
1. Script to update multiple physicians to retired
2. Add your first name initial and last name for @UserName and your user key for @UserKey
3. Spot-check that the physician page looks as it should with 'Retired' in red under name and inactive certs
--Retire multiple physician/certificates
use webcad
declare @listOfIDs1 table (id int);
declare @listOfIDs table (id int);
insert @listOfIDs1(id) values
( 120265 ),
( 120266 ),
( 120269 ),
( 120270 ),
( 120275 ),
( 120280 ),
( 120292 ),
( 120298 ),
( 120300 ),
( 120302 ),
( 120305 ),
( 120311 ),
( 120317 ),
( 120323 ),
( 120325 ),
( 120328 ),
( 120330 ),
( 120332 ),
( 120333 ),
( 120335 ),
( 120339 ),
( 120347 ),
( 120349 ),
( 120351 ),
( 120353 ),
( 120360 ),
( 120365 ),
( 120373 ),
( 120377 ),
( 120379 ),
( 120380 ),
( 120384 ),
( 120386 ),
( 120387 ),
( 120389 ),
( 120391 ),
( 120395 ),
( 120413 ),
( 120415 ),
( 120423 ),
( 120424 ),
( 120426 ),
( 120429 ),
( 120434 ),
( 120435 ),
( 120436 ),
( 120437 ),
( 120440 ),
( 120446 ),
( 120453 ),
( 120482 ),
( 120483 ),
( 120496 ),
( 120498 ),
( 120509 ),
( 120511 ),
( 120512 ),
( 120515 ),
( 120520 ),
( 120522 ),
( 120523 ),
( 120526 ),
( 120527 ),
( 120530 ),
( 120531 ),
( 120535 ),
( 120538 ),
( 120539 ),
( 120542 ),
( 120543 ),
( 120548 ),
( 120549 ),
( 120557 ),
( 120561 ),
( 120562 ),
( 120563 ),
( 120564 ),
( 120566 ),
( 120570 ),
( 120582 ),
( 120584 ),
( 120588 ),
( 120591 ),
( 120592 ),
( 120593 ),
( 120595 ),
( 120596 ),
( 120597 ),
( 120598 ),
( 120600 ),
( 120601 ),
( 120605 ),
( 120609 ),
( 120610 ),
( 120613 ),
( 120614 ),
( 120616 ),
( 120623 ),
( 120624 ),
( 120627 ),
( 120628 ),
( 120629 ),
( 120631 ),
( 120634 ),
( 120635 ),
( 120636 ),
( 120642 ),
( 120649 ),
( 120651 ),
( 120657 ),
( 120663 ),
( 120664 ),
( 120667 ),
( 120669 ),
( 120670 ),
( 120672 ),
( 120678 ),
( 120682 ),
( 120685 ),
( 120686 ),
( 120687 ),
( 120690 ),
( 120691 ),
( 120692 ),
( 120703 ),
( 120705 ),
( 120706 ),
( 120709 ),
( 120710 ),
( 120711 ),
( 120715 ),
( 120718 ),
( 120720 ),
( 120726 ),
( 120727 ),
( 120728 ),
( 120731 ),
( 120732 ),
( 120736 ),
( 120741 ),
( 120750 ),
( 120754 ),
( 120755 ),
( 120762 ),
( 120763 ),
( 120767 ),
( 120775 ),
( 120782 ),
( 120783 ),
( 120786 ),
( 120788 ),
( 120790 ),
( 120792 ),
( 120797 ),
( 120800 ),
( 120804 ),
( 120807 ),
( 120814 ),
( 120816 ),
( 120824 ),
( 120826 ),
( 120827 ),
( 120830 ),
( 120831 ),
( 120833 ),
( 120834 ),
( 120836 ),
( 120840 ),
( 120844 ),
( 120847 ),
( 120849 ),
( 120851 ),
( 120852 ),
( 120856 ),
( 120862 ),
( 120863 ),
( 120866 ),
( 120874 ),
( 120878 ),
( 120881 ),
( 120882 ),
( 120883 ),
( 120888 ),
( 120891 ),
( 120897 ),
( 120899 ),
( 120903 ),
( 120904 ),
( 120906 ),
( 120907 ),
( 120909 ),
( 120910 ),
( 120912 ),
( 120913 ),
( 120914 ),
( 120915 ),
( 120930 ),
( 120933 ),
( 100848 ),
( 100849 ),
( 100852 ),
( 100854 ),
( 100857 ),
( 100865 ),
( 100875 ),
( 100878 ),
( 100880 ),
( 100907 ),
( 100908 ),
( 100919 ),
( 100922 ),
( 100929 ),
( 100930 ),
( 100934 ),
( 100939 ),
( 100942 ),
( 100948 ),
( 100954 ),
( 100956 ),
( 100959 ),
( 100968 ),
( 100969 ),
( 100981 ),
( 100982 ),
( 100985 ),
( 100989 ),
( 100992 ),
( 100993 ),
( 101000 ),
( 101003 ),
( 101007 ),
( 101010 ),
( 101013 ),
( 101030 ),
( 101034 ),
( 101057 ),
( 101065 ),
( 101070 ),
( 101076 ),
( 101083 ),
( 101087 ),
( 101094 ),
( 101095 ),
( 101096 ),
( 101102 ),
( 101103 ),
( 101104 ),
( 101105 ),
( 101106 ),
( 101110 ),
( 101116 ),
( 101120 ),
( 101128 ),
( 101136 ),
( 101137 ),
( 101139 ),
( 101141 ),
( 101148 ),
( 101151 ),
( 101154 ),
( 101155 ),
( 101158 ),
( 101159 ),
( 101166 ),
( 101168 ),
( 101170 ),
( 101171 ),
( 101174 ),
( 101190 ),
( 101193 ),
( 101195 ),
( 101198 ),
( 101202 ),
( 101204 ),
( 101212 ),
( 101215 ),
( 101216 ),
( 101217 ),
( 101219 ),
( 101221 ),
( 101222 ),
( 101226 ),
( 101227 ),
( 101228 ),
( 101231 ),
( 101237 ),
( 101239 ),
( 101242 ),
( 101248 ),
( 101250 ),
( 101252 ),
( 101267 ),
( 101268 ),
( 101269 ),
( 101270 ),
( 101271 ),
( 101272 ),
( 101287 ),
( 101289 ),
( 101293 ),
( 101303 ),
( 101305 ),
( 101314 ),
( 101323 ),
( 101324 ),
( 101336 ),
( 101346 ),
( 101347 ),
( 101361 ),
( 101364 ),
( 101365 ),
( 101372 ),
( 101391 ),
( 101392 ),
( 101395 ),
( 101404 ),
( 101406 ),
( 101411 ),
( 101418 ),
( 101422 ),
( 101424 ),
( 101427 ),
( 101432 ),
( 101452 ),
( 101461 ),
( 101464 ),
( 101469 ),
( 101470 ),
( 101474 ),
( 101475 ),
( 101479 ),
( 101481 ),
( 101489 ),
( 101493 ),
( 101494 ),
( 101497 ),
( 101501 ),
( 101503 ),
( 101504 ),
( 101507 ),
( 101509 ),
( 101515 ),
( 101516 ),
( 101517 ),
( 101521 ),
( 101522 ),
( 101529 ),
( 101542 ),
( 101543 ),
( 101549 ),
( 101551 ),
( 101559 ),
( 101568 ),
( 101569 ),
( 101570 ),
( 101578 ),
( 101579 ),
( 101581 ),
( 101582 ),
( 101592 ),
( 101597 ),
( 101605 ),
( 101610 ),
( 101612 ),
( 101614 ),
( 101617 ),
( 101619 ),
( 101620 ),
( 101625 ),
( 101634 ),
( 101637 ),
( 101638 ),
( 101640 ),
( 101643 ),
( 101644 ),
( 101655 ),
( 101659 ),
( 101661 ),
( 101665 ),
( 101668 ),
( 101679 ),
( 101683 ),
( 101686 ),
( 101689 ),
( 101701 ),
( 101704 ),
( 101713 ),
( 101718 ),
( 101719 ),
( 101722 ),
( 101726 ),
( 101733 ),
( 101736 ),
( 101738 ),
( 101740 ),
( 101745 ),
( 101752 ),
( 101760 ),
( 101767 ),
( 101775 ),
( 101779 ),
( 101781 ),
( 101784 ),
( 101785 ),
( 101800 ),
( 101804 ),
( 101811 ),
( 101817 ),
( 101825 ),
( 101829 ),
( 101840 ),
( 101845 ),
( 101848 ),
( 101850 ),
( 101855 ),
( 101856 ),
( 101857 ),
( 101870 ),
( 101876 ),
( 101881 ),
( 101883 ),
( 101889 ),
( 101896 ),
( 101901 ),
( 101908 ),
( 101927 ),
( 101934 ),
( 101943 ),
( 101948 ),
( 131077 ),
( 131078 ),
( 131079 ),
( 131087 ),
( 131089 ),
( 131091 ),
( 131093 ),
( 131094 ),
( 131095 ),
( 131098 ),
( 131104 ),
( 131110 ),
( 131111 ),
( 131112 ),
( 131117 ),
( 131120 ),
( 131122 ),
( 131123 ),
( 131126 ),
( 131127 ),
( 131128 ),
( 131129 ),
( 131134 ),
( 131136 ),
( 131138 ),
( 131139 ),
( 131144 ),
( 131145 ),
( 131150 ),
( 131151 ),
( 131153 ),
( 131155 ),
( 131160 ),
( 131162 ),
( 131163 ),
( 131164 ),
( 131165 ),
( 131167 ),
( 131169 ),
( 131182 ),
( 131185 ),
( 131190 ),
( 131194 ),
( 131203 ),
( 131204 ),
( 131206 ),
( 131209 ),
( 131211 ),
( 131212 ),
( 131214 ),
( 131218 ),
( 131221 ),
( 131223 ),
( 131226 ),
( 131228 ),
( 131229 ),
( 131230 ),
( 131231 ),
( 131232 ),
( 131233 ),
( 131234 ),
( 131235 ),
( 131239 ),
( 131240 ),
( 131242 ),
( 131249 ),
( 131251 ),
( 131254 ),
( 131255 ),
( 131257 ),
( 131258 ),
( 131259 ),
( 131261 ),
( 131263 ),
( 131266 ),
( 131269 ),
( 131273 ),
( 131291 ),
( 131292 ),
( 131296 ),
( 131298 ),
( 131299 ),
( 131300 ),
( 131301 ),
( 131308 ),
( 131309 ),
( 131311 ),
( 131312 ),
( 131314 ),
( 131317 ),
( 131318 ),
( 131319 ),
( 131320 ),
( 131326 ),
( 131330 ),
( 131332 ),
( 131335 ),
( 131338 ),
( 131339 ),
( 131343 ),
( 131344 ),
( 131345 ),
( 131350 ),
( 131351 ),
( 131353 ),
( 131354 ),
( 131357 ),
( 131360 ),
( 131362 ),
( 131364 ),
( 131365 ),
( 131366 ),
( 131369 ),
( 131375 ),
( 131383 ),
( 131390 ),
( 131391 ),
( 131392 ),
( 131393 ),
( 131394 ),
( 131395 ),
( 131396 ),
( 131398 ),
( 131402 ),
( 131404 ),
( 131407 ),
( 131410 ),
( 131411 ),
( 131413 ),
( 131415 ),
( 131419 ),
( 131422 ),
( 131425 ),
( 131431 ),
( 131434 ),
( 131436 ),
( 131439 ),
( 131440 ),
( 131443 ),
( 131450 ),
( 131451 ),
( 131452 ),
( 131455 ),
( 131456 ),
( 131457 ),
( 131459 ),
( 131462 ),
( 131464 ),
( 131465 ),
( 131466 ),
( 131475 ),
( 131479 ),
( 131485 ),
( 131491 ),
( 131493 ),
( 131504 ),
( 131505 ),
( 131517 ),
( 131518 ),
( 131519 ),
( 131527 ),
( 131533 ),
( 131534 ),
( 131537 ),
( 131541 ),
( 131545 ),
( 131547 ),
( 131549 ),
( 131552 ),
( 131556 ),
( 131557 ),
( 131559 ),
( 131560 ),
( 131563 ),
( 131565 ),
( 131569 ),
( 131571 ),
( 131576 ),
( 131577 ),
( 131579 ),
( 131580 ),
( 131583 ),
( 131588 ),
( 131592 ),
( 131593 ),
( 131594 ),
( 131601 ),
( 131602 ),
( 131603 ),
( 131607 ),
( 131608 ),
( 131609 ),
( 131611 ),
( 131613 ),
( 131614 ),
( 131615 ),
( 131626 ),
( 131635 ),
( 131636 ),
( 131637 ),
( 131642 ),
( 131643 ),
( 131650 ),
( 131651 ),
( 131653 ),
( 131657 ),
( 131659 ),
( 131661 ),
( 131662 ),
( 131664 ),
( 131665 ),
( 131670 ),
( 131671 ),
( 131676 ),
( 131683 ),
( 131687 ),
( 131691 ),
( 131695 ),
( 131696 ),
( 131704 ),
( 131708 ),
( 131711 ),
( 131713 ),
( 131720 ),
( 131721 ),
( 131722 ),
( 131723 ),
( 131729 ),
( 131730 ),
( 131733 ),
( 111272 ),
( 111275 ),
( 111282 ),
( 111284 ),
( 111285 ),
( 111286 ),
( 111294 ),
( 111295 ),
( 111297 ),
( 111307 ),
( 111312 ),
( 111316 ),
( 111321 ),
( 111323 ),
( 111341 ),
( 111342 ),
( 111343 ),
( 111345 ),
( 111348 ),
( 111457 ),
( 111609 ),
( 111614 ),
( 111615 ),
( 111633 ),
( 111640 ),
( 111672 ),
( 111684 ),
( 111696 ),
( 111701 ),
( 111709 ),
( 111717 ),
( 111725 ),
( 111727 ),
( 111737 ),
( 111738 ),
( 111739 ),
( 111741 ),
( 111746 ),
( 111747 ),
( 111754 ),
( 111755 ),
( 111756 ),
( 111764 ),
( 111768 ),
( 111781 ),
( 111784 ),
( 111788 ),
( 111794 ),
( 111808 ),
( 111809 ),
( 111811 ),
( 111812 ),
( 111842 ),
( 111849 ),
( 111850 ),
( 111859 ),
( 111861 ),
( 111862 ),
( 111863 ),
( 111864 ),
( 111865 ),
( 111867 ),
( 111872 ),
( 111874 ),
( 111877 ),
( 111880 ),
( 111892 ),
( 111893 ),
( 111894 ),
( 111896 ),
( 111903 ),
( 111904 ),
( 111905 ),
( 111914 ),
( 111923 ),
( 111930 ),
( 111931 ),
( 111932 ),
( 111940 ),
( 111941 ),
( 111944 ),
( 111948 ),
( 111951 ),
( 111955 ),
( 111957 ),
( 111958 ),
( 111959 ),
( 111963 ),
( 111968 ),
( 111972 ),
( 111976 ),
( 111984 ),
( 111985 ),
( 111988 ),
( 111990 ),
( 111992 ),
( 112005 ),
( 112008 ),
( 112013 ),
( 112017 ),
( 112022 ),
( 112025 ),
( 112026 ),
( 112032 ),
( 112049 ),
( 112052 ),
( 112057 ),
( 112061 ),
( 112067 ),
( 112070 ),
( 112103 ),
( 112112 ),
( 112117 ),
( 112124 ),
( 112129 ),
( 112131 ),
( 112132 ),
( 112134 ),
( 112141 ),
( 112143 ),
( 112158 ),
( 112163 ),
( 112170 ),
( 112172 ),
( 112174 ),
( 112175 ),
( 112187 ),
( 112192 ),
( 112195 ),
( 112197 ),
( 112199 ),
( 112205 ),
( 112208 ),
( 112213 ),
( 112215 ),
( 112221 ),
( 112226 ),
( 112227 ),
( 112229 ),
( 112243 ),
( 100438 ),
( 100441 ),
( 100448 ),
( 100453 ),
( 100454 ),
( 100455 ),
( 100458 ),
( 100466 ),
( 100482 ),
( 100484 ),
( 100494 ),
( 100499 ),
( 100505 ),
( 100510 ),
( 100527 ),
( 100528 ),
( 100534 ),
( 100545 ),
( 100547 ),
( 100551 ),
( 100553 ),
( 100555 ),
( 100558 ),
( 100564 ),
( 100569 ),
( 100570 ),
( 100573 ),
( 100577 ),
( 100583 ),
( 100584 ),
( 100587 ),
( 100593 ),
( 100598 ),
( 100599 ),
( 100604 ),
( 100605 ),
( 100608 ),
( 100624 ),
( 100625 ),
( 100627 ),
( 100631 ),
( 100632 ),
( 100637 ),
( 100654 ),
( 100656 ),
( 100677 ),
( 100678 ),
( 100689 ),
( 100692 ),
( 100693 ),
( 100701 ),
( 100706 ),
( 100707 ),
( 100718 ),
( 100720 ),
( 100721 ),
( 100735 ),
( 100740 ),
( 100741 ),
( 100742 ),
( 100744 ),
( 100749 ),
( 100750 ),
( 100754 ),
( 100758 ),
( 100772 ),
( 100776 ),
( 100778 ),
( 100781 ),
( 100782 ),
( 100785 ),
( 100787 ),
( 100788 ),
( 100793 ),
( 100795 ),
( 100808 ),
( 100812 ),
( 100817 ),
( 100827 ),
( 100829 ),
( 100841 ),
( 100844 ),
( 100845 ),
( 100846 ),
( 100847 ),
( 110599 ),
( 110615 ),
( 110616 ),
( 110645 ),
( 110657 ),
( 110658 ),
( 110660 ),
( 110672 ),
( 110674 ),
( 110680 ),
( 110695 ),
( 110703 ),
( 110706 ),
( 110710 ),
( 110711 ),
( 110723 ),
( 110726 ),
( 110729 ),
( 110736 ),
( 110738 ),
( 110740 ),
( 110741 ),
( 110747 ),
( 110753 ),
( 110762 ),
( 110771 ),
( 110781 ),
( 110782 ),
( 110787 ),
( 110797 ),
( 110813 ),
( 110814 ),
( 110816 ),
( 110824 ),
( 110826 ),
( 110829 ),
( 110837 ),
( 110839 ),
( 110844 ),
( 110849 ),
( 110855 ),
( 110859 ),
( 110866 ),
( 110867 ),
( 110868 ),
( 110870 ),
( 110871 ),
( 110879 ),
( 110881 ),
( 110884 ),
( 110892 ),
( 110894 ),
( 110902 ),
( 110903 ),
( 110906 ),
( 110910 ),
( 110913 ),
( 110916 ),
( 110917 ),
( 110923 ),
( 110924 ),
( 110935 ),
( 110938 ),
( 110940 ),
( 110948 ),
( 110965 ),
( 110968 ),
( 110977 ),
( 110982 ),
( 110983 ),
( 110990 ),
( 110991 ),
( 110997 ),
( 111001 ),
( 111007 ),
( 111009 ),
( 111015 ),
( 111022 ),
( 111025 ),
( 111028 ),
( 111029 ),
( 111034 ),
( 111045 ),
( 111050 ),
( 111053 ),
( 111055 ),
( 111062 ),
( 111063 ),
( 111066 ),
( 111067 ),
( 111069 ),
( 111071 ),
( 111075 ),
( 111078 ),
( 111085 ),
( 111090 ),
( 111102 ),
( 111104 ),
( 111106 ),
( 111107 ),
( 111111 ),
( 111113 ),
( 111128 ),
( 111130 ),
( 111131 ),
( 111136 ),
( 111151 ),
( 111157 ),
( 111160 ),
( 111164 ),
( 111168 ),
( 111169 ),
( 111178 ),
( 111182 ),
( 111193 );
DECLARE @PersonKey AS INT
DECLARE @EffectiveDate AS DATETIME
DECLARE @UserName AS VARCHAR(20)
DECLARE @UserKey AS INT
SET @EffectiveDate = GETDATE()
SET @UserName = 'aagrawal'
SET @UserKey = 193
INSERT INTO @listOfIDs ([id]) SELECT t1.id from @listOfIDs1 t1 INNER JOIN Person P
ON P.Person_Key = t1.Id AND P.Deceased_Flag = 0
SELECT * FROM @listOfIDs
UPDATE [Certificate]
SET CertificateStatus = 2, Diplomate_History_Status_Key = 8, ExpirationDate = @EffectiveDate, UpdatedBy = @UserName, UpdatedDate = GETDATE()
WHERE Person_Key IN (select id from @listOfIDs) AND CertificateStatus = 1
INSERT INTO Diplomate_History (Diplomate_Key, Candidacy_Key, Diplomate_History_Status_Key, Diplomate_History_Status_Date, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, Diplomate_History_Expiration_Date, Note,
Date_Added, Date_Updated, User_Key)
SELECT Diplomate_History.Diplomate_Key, Candidacy_Key, 8, @EffectiveDate, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, @Effectivedate, 'Physician updated to retired', GETDATE(), GETDATE(), User_Key
FROM Diplomate_History
INNER JOIN (SELECT Diplomate_History.Diplomate_Key, MAX(Diplomate_History.Diplomate_History_Key) AS MaxDipKey FROM Diplomate
INNER JOIN Diplomate_History ON Diplomate_History.Diplomate_Key = Diplomate.Diplomate_Key
WHERE Diplomate.Person_Key IN (select id from @listOfIDs)
GROUP BY Diplomate_History.Diplomate_Key) AS MaxDip ON MaxDip.MaxDipKey = Diplomate_History.Diplomate_History_Key
WHERE Diplomate_History.Diplomate_History_Expiration_Date > GETDATE()
UPDATE Person
SET CurrentCCEnrollmentId = NULL,
Note = CONCAT(Note, '; Updated to Retired by IT')
WHERE Person_Key IN (select id from @listOfIDs) AND Deceased_Flag != 1;
UPDATE Diplomate
SET Clinical_Status_Id = 3, Date_Updated = @EffectiveDate, User_key = @UserKey
WHERE Person_Key IN (select id from @listOfIDs);