1 of 2: Update individual
1. Script to update a physician to deceased
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 'Deceased' in red under name and inactive certs.
4. If you need to update a physician who is already retired with inactive certs, this query won't update everything because of the WHERE clause (where CertificateStatus = 1); if this is the case, null out the update to Certificate and run the rest
USE WEBCAD
DECLARE @PersonKey AS INT
DECLARE @EffectiveDate AS DATETIME
DECLARE @UserName AS VARCHAR(20)
DECLARE @UserKey AS INT
SET @PersonKey = 139501
SET @EffectiveDate = GETDATE()
SET @UserName = {{'Your name: like JVollmer'}};
SET @UserKey = {{Your User Key: like 188}}
UPDATE [Certificate] SET CertificateStatus = 2, ExpirationDate = @EffectiveDate, UpdatedBy = @UserName, UpdatedDate = GETDATE()
WHERE Person_Key = @PersonKey AND CertificateStatus = 1
--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
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, 1, @EffectiveDate, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, @Effectivedate, 'Updated physician to deceased',
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()
UPDATE Person SET Deceased_Flag = 1
, Deceased_Notification_Date = @EffectiveDate
, CurrentCCEnrollmentId = NULL
, UserId = NULL
, Email_Address_Valid_Flag = 0
, Mailing_opt_out = 1
, UpdatedBy = @UserName
, UpdatedDate = GETDATE()
, Note = CONCAT(Note, '; Updated to deceased by IT')
WHERE Person_Key = @PersonKey;
UPDATE Diplomate
SET Date_Updated = @EffectiveDate, User_key = @UserKey
WHERE Person_Key = @PersonKey;
DELETE from AspNetUsers
WHERE UserName = (SELECT Email_Address from Person WHERE Person_Key = @PersonKey);
--------------------------------------------------------------------------------------------------------------------------
2 of 2: Update multiple
1. Script to update multiple physicians to deceased
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 'Deceased' in red under name and inactive certs.
4. If you need to update a physician who is already retired with inactive certs, this query won't update everything because of the WHERE clause (where CertificateStatus = 1); if this is the case, null out the update to Certificate and run the rest
--Update multiple physicians to deceased
use webcad
declare @listOfIDs table (id int);
insert @listOfIDs(id) values
( 120273 ),
( 120299 ),
( 120308 ),
( 120342 ),
( 120361 ),
( 120410 ),
( 120414 ),
( 120486 ),
( 120492 ),
( 120574 ),
( 120590 ),
( 120647 ),
( 120671 ),
( 120760 ),
( 120795 ),
( 120809 ),
( 120817 ),
( 120928 ),
( 120929 ),
( 100976 ),
( 100979 ),
( 101018 ),
( 101020 ),
( 101132 ),
( 101163 ),
( 101169 ),
( 101176 ),
( 101210 ),
( 101274 ),
( 101330 ),
( 101423 ),
( 101477 ),
( 101484 ),
( 101505 ),
( 101572 ),
( 101573 ),
( 101648 ),
( 101653 ),
( 101658 ),
( 101677 ),
( 101710 ),
( 101787 ),
( 101803 ),
( 101820 ),
( 101854 ),
( 101867 ),
( 131086 ),
( 131106 ),
( 131149 ),
( 131154 ),
( 131157 ),
( 131168 ),
( 131181 ),
( 131186 ),
( 131201 ),
( 131207 ),
( 131217 ),
( 131262 ),
( 131264 ),
( 131270 ),
( 131274 ),
( 131275 ),
( 131279 ),
( 131283 ),
( 131285 ),
( 131356 ),
( 131363 ),
( 131368 ),
( 131405 ),
( 131432 ),
( 131433 ),
( 131441 ),
( 131470 ),
( 131499 ),
( 131500 ),
( 131542 ),
( 131553 ),
( 131558 ),
( 131564 ),
( 131590 ),
( 131599 ),
( 131718 ),
( 131727 ),
( 111291 ),
( 111299 ),
( 111302 ),
( 111303 ),
( 111310 ),
( 111319 ),
( 111322 ),
( 111330 ),
( 111331 ),
( 111339 ),
( 111361 ),
( 111362 ),
( 111391 ),
( 111412 ),
( 111430 ),
( 111444 ),
( 111451 ),
( 111456 ),
( 111471 ),
( 111472 ),
( 111475 ),
( 111476 ),
( 111491 ),
( 111492 ),
( 111493 ),
( 111503 ),
( 111506 ),
( 111508 ),
( 111510 ),
( 111511 ),
( 111515 ),
( 111520 ),
( 111522 ),
( 111523 ),
( 111525 ),
( 111530 ),
( 111535 ),
( 111541 ),
( 111543 ),
( 111546 ),
( 111548 ),
( 111549 ),
( 111550 ),
( 111552 ),
( 111558 ),
( 111559 ),
( 111562 ),
( 111563 ),
( 111570 ),
( 111572 ),
( 111582 ),
( 111584 ),
( 111585 ),
( 111586 ),
( 111591 ),
( 111592 ),
( 111594 ),
( 111595 ),
( 111596 ),
( 111602 ),
( 111605 ),
( 111617 ),
( 111619 ),
( 111620 ),
( 111621 ),
( 111626 ),
( 111627 ),
( 111629 ),
( 111630 ),
( 111637 ),
( 111638 ),
( 111647 ),
( 111649 ),
( 111651 ),
( 111656 ),
( 111661 ),
( 111663 ),
( 111665 ),
( 111670 ),
( 111671 ),
( 111674 ),
( 111677 ),
( 111681 ),
( 111683 ),
( 111688 ),
( 111692 ),
( 111697 ),
( 111699 ),
( 111706 ),
( 111708 ),
( 111710 ),
( 111714 ),
( 111732 ),
( 111743 ),
( 111745 ),
( 111749 ),
( 111753 ),
( 111760 ),
( 111765 ),
( 111766 ),
( 111769 ),
( 111770 ),
( 111773 ),
( 111774 ),
( 111775 ),
( 111777 ),
( 111778 ),
( 111779 ),
( 111785 ),
( 111786 ),
( 111792 ),
( 111798 ),
( 111803 ),
( 111806 ),
( 111818 ),
( 111822 ),
( 111825 ),
( 111830 ),
( 111835 ),
( 111841 ),
( 111847 ),
( 111857 ),
( 111871 ),
( 111873 ),
( 111876 ),
( 111881 ),
( 111883 ),
( 111884 ),
( 111897 ),
( 111900 ),
( 111907 ),
( 111910 ),
( 111912 ),
( 111919 ),
( 111920 ),
( 111921 ),
( 111942 ),
( 111964 ),
( 111965 ),
( 111967 ),
( 111969 ),
( 111970 ),
( 111974 ),
( 111989 ),
( 111993 ),
( 112007 ),
( 112012 ),
( 112028 ),
( 112033 ),
( 112037 ),
( 112041 ),
( 112042 ),
( 112043 ),
( 112054 ),
( 112058 ),
( 112074 ),
( 112086 ),
( 112105 ),
( 112142 ),
( 112149 ),
( 112154 ),
( 112176 ),
( 112183 ),
( 112200 ),
( 112204 ),
( 112233 ),
( 112235 ),
( 112237 ),
( 100538 ),
( 100542 ),
( 100562 ),
( 100582 ),
( 100615 ),
( 100736 ),
( 100765 ),
( 110485 ),
( 110489 ),
( 110492 ),
( 110493 ),
( 110494 ),
( 110506 ),
( 110510 ),
( 110513 ),
( 110514 ),
( 110523 ),
( 110535 ),
( 110537 ),
( 110544 ),
( 110556 ),
( 110559 ),
( 110560 ),
( 110562 ),
( 110566 ),
( 110567 ),
( 110568 ),
( 110571 ),
( 110576 ),
( 110579 ),
( 110581 ),
( 110585 ),
( 110588 ),
( 110603 ),
( 110604 ),
( 110605 ),
( 110606 ),
( 110608 ),
( 110614 ),
( 110618 ),
( 110619 ),
( 110621 ),
( 110626 ),
( 110627 ),
( 110628 ),
( 110629 ),
( 110631 ),
( 110632 ),
( 110634 ),
( 110637 ),
( 110641 ),
( 110646 ),
( 110648 ),
( 110652 ),
( 110653 ),
( 110655 ),
( 110662 ),
( 110668 ),
( 110673 ),
( 110676 ),
( 110681 ),
( 110682 ),
( 110683 ),
( 110687 ),
( 110690 ),
( 110692 ),
( 110694 ),
( 110696 ),
( 110700 ),
( 110705 ),
( 110712 ),
( 110713 ),
( 110730 ),
( 110731 ),
( 110733 ),
( 110735 ),
( 110739 ),
( 110742 ),
( 110750 ),
( 110754 ),
( 110755 ),
( 110756 ),
( 110759 ),
( 110760 ),
( 110761 ),
( 110766 ),
( 110768 ),
( 110770 ),
( 110774 ),
( 110778 ),
( 110779 ),
( 110784 ),
( 110789 ),
( 110791 ),
( 110793 ),
( 110802 ),
( 110807 ),
( 110809 ),
( 110810 ),
( 110817 ),
( 110819 ),
( 110821 ),
( 110823 ),
( 110825 ),
( 110827 ),
( 110834 ),
( 110838 ),
( 110843 ),
( 110846 ),
( 110850 ),
( 110853 ),
( 110854 ),
( 110857 ),
( 110858 ),
( 110893 ),
( 110895 ),
( 110896 ),
( 110897 ),
( 110900 ),
( 110931 ),
( 110934 ),
( 110939 ),
( 110954 ),
( 110960 ),
( 110985 ),
( 110993 ),
( 111016 ),
( 111027 ),
( 111051 ),
( 111074 ),
( 111080 ),
( 111093 ),
( 111100 ),
( 111101 ),
( 111108 ),
( 111110 ),
( 111121 ),
( 111137 ),
( 111140 ),
( 111143 ),
( 111144 ),
( 111148 ),
( 111149 ),
( 111150 ),
( 111152 ),
( 111153 ),
( 111155 ),
( 111158 ),
( 111159 ),
( 111167 ),
( 111172 ),
( 111176 ),
( 111179 ),
( 111183 ),
( 111192 ),
( 111195 ),
( 111201 ),
( 111206 ),
( 111207 ),
( 111208 ),
( 111222 ),
( 111241 ),
( 111252 ),
( 111255 ),
( 111266 ),
( 118906 ),
( 118910 ),
( 118917 ),
( 118929 ),
( 118930 ),
( 118938 ),
( 118940 ),
( 118952 ),
( 118970 ),
( 119017 ),
( 119026 ),
( 119050 ),
( 119087 ),
( 129107 ),
( 129109 ),
( 129110 ),
( 129111 ),
( 129112 ),
( 129113 ),
( 129114 ),
( 129115 ),
( 129116 ),
( 129117 ),
( 129118 ),
( 129121 ),
( 129123 ),
( 129125 ),
( 129128 ),
( 129129 ),
( 129130 ),
( 129131 ),
( 129132 ),
( 129133 ),
( 129136 ),
( 129137 ),
( 129139 ),
( 129140 ),
( 129141 ),
( 129143 ),
( 129144 ),
( 129146 ),
( 129147 ),
( 129152 ),
( 129153 ),
( 129155 ),
( 129160 ),
( 129161 ),
( 129163 ),
( 129165 ),
( 129171 ),
( 129172 ),
( 129175 ),
( 129176 ),
( 129177 ),
( 129178 ),
( 129186 ),
( 129208 ),
( 129211 ),
( 129256 ),
( 129360 ),
( 129437 ),
( 129752 ),
( 129825 ),
( 129828 ),
( 129829 ),
( 129830 ),
( 129832 ),
( 129834 ),
( 129835 ),
( 129839 ),
( 129848 ),
( 129850 ),
( 129851 ),
( 129852 ),
( 129853 ),
( 129854 ),
( 129855 ),
( 129856 ),
( 129857 ),
( 129858 ),
( 129859 ),
( 129860 ),
( 129861 ),
( 129862 ),
( 129863 ),
( 129864 ),
( 129865 ),
( 129866 ),
( 129868 ),
( 129869 ),
( 129870 ),
( 129871 ),
( 129872 ),
( 129873 ),
( 129874 ),
( 129875 ),
( 129877 ),
( 129878 ),
( 129879 ),
( 129881 ),
( 129885 ),
( 129887 ),
( 129891 ),
( 129893 ),
( 129894 ),
( 129896 ),
( 129897 ),
( 129898 ),
( 129899 ),
( 129901 ),
( 129903 ),
( 129904 ),
( 129905 ),
( 129906 ),
( 129907 ),
( 129908 ),
( 129909 ),
( 129911 ),
( 129912 ),
( 129913 ),
( 129914 ),
( 129915 ),
( 129918 ),
( 129920 ),
( 129928 ),
( 129929 ),
( 129931 ),
( 129938 ),
( 129941 ),
( 129942 ),
( 129950 ),
( 129951 ),
( 129953 ),
( 129954 ),
( 129956 ),
( 129957 ),
( 129958 ),
( 129966 ),
( 129976 ),
( 129985 ),
( 130000 ),
( 130001 ),
( 130006 ),
( 130018 ),
( 130022 ),
( 130024 ),
( 130028 ),
( 130034 ),
( 130073 ),
( 130080 ),
( 130099 ),
( 130106 ),
( 130109 ),
( 130111 ),
( 130118 ),
( 130121 ),
( 130127 ),
( 130129 ),
( 130130 ),
( 130132 ),
( 130136 ),
( 130162 ),
( 130190 ),
( 130193 ),
( 130200 ),
( 130208 ),
( 130211 ),
( 130216 ),
( 130219 ),
( 130232 ),
( 130240 ),
( 130270 ),
( 115455 ),
( 115631 ),
( 115761 ),
( 115829 ),
( 115901 ),
( 115988 ),
( 116031 ),
( 116034 ),
( 116039 ),
( 116205 ),
( 116273 ),
( 116492 ),
( 123785 ),
( 123846 ),
( 123907 ),
( 124051 ),
( 124342 ),
( 124383 ),
( 124430 ),
( 124468 ),
( 124482 ),
( 124497 ),
( 124608 ),
( 124610 ),
( 124676 ),
( 124691 ),
( 124706 ),
( 124843 ),
( 124889 ),
( 125125 ),
( 125189 ),
( 125227 ),
( 125228 ),
( 125387 ),
( 125471 ),
( 115656 ),
( 115657 ),
( 115659 ),
( 115661 ),
( 115663 ),
( 115666 ),
( 115673 ),
( 115687 ),
( 115695 ),
( 115697 ),
( 115699 ),
( 115703 ),
( 115705 ),
( 115706 ),
( 115707 ),
( 115708 ),
( 115709 ),
( 115710 ),
( 115713 ),
( 115716 ),
( 115718 ),
( 115720 ),
( 115722 ),
( 115724 ),
( 115726 ),
( 115730 ),
( 115732 ),
( 115735 ),
( 115740 ),
( 115741 ),
( 115745 ),
( 115749 ),
( 115751 ),
( 115754 ),
( 115755 ),
( 115757 ),
( 115760 ),
( 115767 ),
( 115769 ),
( 115770 ),
( 115772 ),
( 115779 ),
( 115786 ),
( 115789 ),
( 115790 ),
( 115792 ),
( 115796 ),
( 115799 ),
( 115802 ),
( 115806 ),
( 115812 ),
( 115815 ),
( 115818 ),
( 115830 ),
( 115834 ),
( 115838 ),
( 115842 ),
( 115844 ),
( 115845 ),
( 115850 ),
( 115852 ),
( 115854 ),
( 115855 ),
( 115856 ),
( 115857 ),
( 115861 ),
( 115866 ),
( 115867 ),
( 115868 ),
( 115869 ),
( 115880 ),
( 115884 ),
( 115887 ),
( 115888 ),
( 115890 ),
( 115893 ),
( 115898 ),
( 115905 ),
( 115909 ),
( 115912 ),
( 115925 ),
( 115926 ),
( 115930 ),
( 115935 ),
( 115936 ),
( 115937 ),
( 115939 ),
( 115940 ),
( 115941 ),
( 115942 ),
( 115949 ),
( 115953 ),
( 115954 ),
( 115962 ),
( 115963 ),
( 115967 ),
( 115973 ),
( 115975 ),
( 115977 ),
( 115990 ),
( 115993 ),
( 115995 ),
( 115996 ),
( 115999 ),
( 116001 ),
( 116008 ),
( 116009 ),
( 116015 ),
( 116019 ),
( 116021 ),
( 116026 ),
( 116029 ),
( 116033 ),
( 116040 ),
( 116054 ),
( 116063 ),
( 116064 ),
( 116067 ),
( 116076 ),
( 116082 ),
( 116088 ),
( 116092 ),
( 116100 ),
( 116103 ),
( 116107 ),
( 116169 ),
( 116211 ),
( 116222 ),
( 116225 ),
( 116230 ),
( 116231 ),
( 116246 ),
( 116249 ),
( 116250 ),
( 116255 ),
( 116265 ),
( 116270 ),
( 116276 ),
( 116277 ),
( 116278 ),
( 116280 ),
( 116303 ),
( 116308 ),
( 116315 ),
( 116316 ),
( 116317 ),
( 116321 ),
( 116324 ),
( 116325 ),
( 116327 ),
( 116328 ),
( 116331 ),
( 116335 ),
( 116338 ),
( 116340 ),
( 116345 ),
( 116347 ),
( 116352 ),
( 116357 ),
( 116358 ),
( 116361 ),
( 116362 ),
( 116368 ),
( 116369 ),
( 116370 ),
( 116372 ),
( 116375 ),
( 116376 ),
( 116377 ),
( 116378 ),
( 116380 ),
( 116381 ),
( 116383 ),
( 116386 ),
( 116389 ),
( 116390 ),
( 116399 ),
( 116400 ),
( 116402 ),
( 116404 ),
( 116406 ),
( 116407 ),
( 116408 ),
( 116410 ),
( 116411 ),
( 116412 ),
( 116414 ),
( 116416 ),
( 116417 ),
( 116419 ),
( 116420 ),
( 116424 ),
( 116427 ),
( 116428 ),
( 116429 ),
( 116430 ),
( 116435 ),
( 116436 ),
( 116438 ),
( 116440 ),
( 116441 ),
( 116443 ),
( 116445 ),
( 116448 ),
( 116451 ),
( 116452 ),
( 116453 ),
( 116456 ),
( 116457 ),
( 116458 ),
( 116461 ),
( 116462 ),
( 116463 ),
( 116464 ),
( 116465 ),
( 116466 ),
( 116467 ),
( 116468 ),
( 116471 ),
( 116472 ),
( 116479 ),
( 116480 ),
( 116481 ),
( 116483 ),
( 116486 ),
( 116491 ),
( 116493 ),
( 116495 ),
( 116496 ),
( 116498 ),
( 116500 ),
( 116502 ),
( 116513 ),
( 116514 ),
( 116515 ),
( 116522 ),
( 116523 ),
( 116524 ),
( 116525 ),
( 116527 ),
( 116528 ),
( 116532 ),
( 116533 ),
( 116536 ),
( 116538 ),
( 116539 ),
( 116540 ),
( 116542 ),
( 116544 ),
( 116546 ),
( 116547 ),
( 116548 ),
( 116557 ),
( 116565 ),
( 116568 ),
( 116569 ),
( 116570 ),
( 116572 ),
( 116574 ),
( 116579 ),
( 116582 ),
( 116585 ),
( 116587 ),
( 116590 ),
( 116593 ),
( 116594 ),
( 116595 ),
( 116598 ),
( 116603 ),
( 116605 ),
( 116607 ),
( 116608 ),
( 116609 ),
( 116612 ),
( 116613 ),
( 116616 ),
( 116617 ),
( 116618 ),
( 116619 ),
( 116620 ),
( 116621 ),
( 116623 ),
( 116626 ),
( 116632 ),
( 116634 ),
( 116636 ),
( 116640 ),
( 116642 ),
( 116643 ),
( 116648 ),
( 116650 ),
( 116657 ),
( 116660 ),
( 116663 ),
( 116667 ),
( 116668 ),
( 116670 ),
( 125418 ),
( 116654 ),
( 123727 ),
( 124284 ),
( 123849 ),
( 116342 ),
( 116289 ),
( 123911 ),
( 124015 ),
( 116257 ),
( 124925 ),
( 123594 ),
( 124539 ),
( 116235 ),
( 124105 ),
( 124007 ),
( 124077 ),
( 123988 ),
( 123837 ),
( 123552 ),
( 123901 ),
( 123702 ),
( 123811 ),
( 123549 ),
( 125153 ),
( 123782 ),
( 116307 ),
( 124037 ),
( 123871 ),
( 123796 ),
( 123919 ),
( 123697 ),
( 123897 ),
( 116261 ),
( 123958 ),
( 123687 ),
( 116313 ),
( 123957 ),
( 123938 ),
( 125320 ),
( 123884 ),
( 123905 ),
( 123932 ),
( 123983 ),
( 124266 ),
( 124337 ),
( 123942 ),
( 123815 ),
( 125012 ),
( 124020 ),
( 125075 ),
( 116274 ),
( 116348 ),
( 123690 ),
( 123682 ),
( 123816 ),
( 116305 ),
( 116312 ),
( 123978 ),
( 123934 ),
( 123814 ),
( 123890 ),
( 123686 ),
( 123916 ),
( 124125 ),
( 123766 ),
( 123974 );
DECLARE @PersonKey AS VARCHAR(MAX)
DECLARE @EffectiveDate AS DATETIME
DECLARE @UserName AS VARCHAR(20)
DECLARE @UserKey AS INT
SET @EffectiveDate = GETDATE()
SET @UserName = 'aagrawal'
SET @UserKey = 193
UPDATE [Certificate] SET CertificateStatus = 2, 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, 1, @EffectiveDate, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, @Effectivedate, 'Physician updated to deceased',
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 Deceased_Flag = 1
, Deceased_Notification_Date = @EffectiveDate
, CurrentCCEnrollmentId = NULL
, UserId = NULL
, Email_Address_Valid_Flag = 0
, Mailing_opt_out = 1
, UpdatedBy = @UserName
, UpdatedDate = GETDATE()
, Note = CONCAT(Note, '; Updated to deceased by IT') --use if proof of death URL
WHERE Person_Key IN (select id from @listOfIDs);
UPDATE Diplomate SET Clinical_Status_Id = 3, Date_Updated = @EffectiveDate, User_key = @UserKey WHERE Person_Key IN (select id from @listOfIDs);
DELETE from AspNetUsers where UserName IN (SELECT Email_Address from Person WHERE Person_Key IN (select id from @listOfIDs));