How to remove overlapping rows based on date and keep most recent in sql?
How to remove overlapping rows based on date and keep most recent in sql? I need to find all episode_ids for every patient. However, when an overlapping episode arises within 90 days of the previous, then I just want to keep the most recent episode. For example, patient_num 3242 below has 3 episodes: the second episode overlaps with the first episode within 90 days, and the third episode overlaps with the second episode within 90 days, in this situation I need to just keep the 3rd episode. patient_num 3242 CREATE TABLE table1 (episode_id nvarchar(max), patient_num nvarchar(max), admit_date date, discharge_date date) INSERT INTO table1 (episode_id, patient_num , admit_date , discharge_date ) VALUES ('1','5743','1/1/2016','1/5/2016'), ('2','5743','4/26/2016','4/29/2016'), ('3','5743','5/26/2016','5/28/2016'), ('4','5743','9/21/2016','9/28/2016'), ('5'...