Multi tool use

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','8859','4/27/2016','5/5/2016'),
('6','3242','4/28/2016','4/29/2016'),
('7','3242','11/21/2016','11/23/2016'),
('8','3242','11/24/2016','11/29/2016'),
('9','3242','12/12/2016','12/29/2016')


Initial Table (table1)

episode_id patient_num admit_date discharge_date
1 5743 2016-01-01 2016-01-05
2 5743 2016-04-26 2016-04-29
3 5743 2016-05-26 2016-05-28
4 5743 2016-09-21 2016-09-28
5 8859 2016-04-27 2016-05-05
6 3242 2016-04-28 2016-04-29
7 3242 2016-11-21 2016-11-23
8 3242 2016-11-24 2016-11-29
9 3242 2016-12-12 2016-12-29


expected result

episode_id patient_num admit_date discharge_date
1 5743 2016-01-01 2016-01-05
3 5743 2016-05-26 2016-05-28
4 5743 2016-09-21 2016-09-28
5 8859 2016-04-27 2016-05-05
6 3242 2016-04-28 2016-04-29
9 3242 2016-12-12 2016-12-29


My attempt:

SELECT *
FROM table1 AS a
WHERE EXISTS
(
SELECT *
FROM table1 AS b
WHERE a.episode_id != b.episode_id
AND a.patient_num= b.patient_num
AND a.admit_date BETWEEN b.discharge_date AND DATEADD(DAY, 90, b.discharge_date ))


There error in my script is that for patient num 3242, I am getting both episode id 8 and 9 where I only want episode 9. I am assuming the reason for this error is that I am comparing each row individually instead of as a group but I am having trouble grouping. Additionally, this script is not showing instances where there is not an overlap, such as the episode_id 1, 4, 5, 6. Any advice on this approach?

3242

2 Answers
2

I removed the cursor solution here since it has low perforamnce
a solution without using Cursor is:

WITH ExcludedIds AS (
SELECT DISTINCT T2.episode_id
FROM table1 AS T
INNER JOIN table1 AS T2 ON T.episode_id != T2.episode_id
AND T.patient_num = T2.patient_num
AND T2.discharge_date BETWEEN DATEADD(DAY, -90, T.admit_date ) AND T.discharge_date)

SELECT T.episode_id, T.patient_num, T.admit_date, T.discharge_date
FROM table1 AS T
WHERE T.episode_id NOT IN (SELECT ExcludedIds.episode_id FROM ExcludedIds)


Thought understanding this solution is a bit hard.

I think not exists does what you want:

not exists

SELECT a.*
FROM table1 a
WHERE NOT EXISTS (SELECT 1
FROM table1 b
WHERE a.episode_id <> b.episode_id AND
a.patient_num = b.patient_num AND
b.admin_date < a.discharge_date AND
b.discharge_date >= DATEADD(DAY, -90, a.discharge_date)
);


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.