SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   Name                      VARCHAR(75) NOT NULL 
  ,ID                        INTEGER 
  ,Membership_Effective_Date DATE 
  ,Membership_End_Date       DATE 
);
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Bob',1,'1/1/2020',NULL);
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Bob',1,'1/1/2017','1/2/2017');
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Bob',1,'1/1/2017','9/1/2018');
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Kim',2,'1/1/2019','1/1/2020');
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Kim',2,'1/1/2019','12/31/2019');
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Susan',3,'1/1/2018','12/31/2018');
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Susan',3,'1/1/2019','1/1/2019');
INSERT INTO mytable(Name,ID,Membership_Effective_Date,Membership_End_Date) VALUES ('Larry',4,'1/1/2020','1/1/2020');

8 rows affected

name	id	membership_effective_date	membership_end_date
Bob	1	2020-01-01	null
Kim	2	2019-01-01	2020-01-01
Susan	3	2018-01-01	2018-12-31
Larry	4	2020-01-01	2020-01-01